String manupulation

Transact-SQL
1

Hi all,
below is my query expecting result as test;test2

thanks in advance

create table table_1
(
remarks varchar(max),
month int,
year int
)

insert into table_1 values('',1,2020)
insert into table_1 values('test',1,2020)
insert into table_1 values('',1,2020)
insert into table_1 values('test2',1,2020)
insert into table_1 values('',1,2020)
insert into table_1 values('',1,2020)
insert into table_1 values('',1,2020)

declare @temp table
(
rowid int identity(1,1),
remarks varchar(max)
)

declare @min int
declare @max int
declare @rem varchar(max)
declare @remarks nvarchar(max)

insert into @temp
select remarks
from table_1
where month = 1 and year = 2020

select @min = min(rowid) from @temp

select @max = max(rowid) from @temp

while (@min < = @max)
begin

select @rem = remarks from @temp
where Rowid = @min

set @remarks = concat(@remarks,';',@rem)

set @min = @min + 1
end

select @remarks

2 
select stuff((
    select ';' + remarks
    from table_1
    where remarks <> ''
    order by year, month
    for xml path(''), type).value('.', 'varchar(max)'),
    1, 1, '')
1 Like