sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | CURSOR : concatenate with group by

DECLARE @group TABLE
(
  [values] varchar(8000),
  [group] int
)

if exists (select * from tempdb.sys.tables t where t.name like '#groups%')
 drop table dbo.#groups

;with a(value, [group])
as
(
select * from (values ('a', 1), ('b', 1), ('c', 1), ('d', '2'), ('e', 2)) a(value, [group])
)

select * into #groups from a
--select * from #groups
insert into @group select distinct null, [group] from #groups

declare @g int
declare cur cursor for select [group] from @group
open cur
fetch next from cur into @g

WHILE(@@FETCH_STATUS=0)
BEGIN

declare @str varchar(8000) = ''
select @str += ' ' + value from #groups where [group] = @g
--select @str a
update @group set [values] = @str where [group] = @g
fetch next from cur into @g

END
CLOSE CUR
DEALLOCATE CUR

select * from @group
Comments