Below is a code i use now. In real life @stab table is big (and it is normal table not variable) and there are many concatenated columns, not only 2. I wonder is there any better solution than I use in this example . I use old MSSQL so STRING_AGG() is not available. My result is correct but i was wondering is there any better method like selecting into one XML and then select columns from this one XML.
declare @stab table (id int , v1 varchar(10),v2 varchar(10)) declare @xmlstring xml insert into @stab values (1,'aaa','1111'),(1,'bbbb','2222'),(2,'cccc','3333'),(2,'dddd','4444') --select * from @stab t1 select id, stuff((select ','+v1 from @stab t2 where t1.id=t2.id for xml path('')) ,1,1,'') ,stuff((select ','+v2 from @stab t2 where t1.id=t2.id for xml path('')) ,1,1,'') from @stab t1 group by id