How to get more then one concatenated column from table rows

Question

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

 

Answer 1

An alternative way would be

WITH T AS
(
select id,
       x= (SELECT ',' + v1 AS v1 , 
                  ',' + v2 AS v2 
           FROM @stab t2 
           WHERE t1.id=t2.id FOR XML PATH(''), type) 
from @stab t1 
group by id
)
select T.id
       , stuff(T.x.query('v1/text()').value('text()[1]', 'varchar(max)') ,1,1,'')
       , stuff(T.x.query('v2/text()').value('text()[1]', 'varchar(max)') ,1,1,'')
from T

I'm not claiming that it is in any way "better" than what you currently have though.

On the positive side it does not need to do as many lookups against @stab as it brings back multiple columns at once. On the negative in terms of estimated plan cost it is costed significantly more but for XML operators this is especially unreliable. You'd need to test against your actual data and see if better/worse.