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

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

  • 1
    "I use old MSSQL" - How old? What version are you on? Jun 28 at 15:09

1 Answer 1


An alternative way would be

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.

  • Thanks a lot.. This is exactly what I was looking for. I was close but I didn't know i can use .query,value and I thought i need to use one or the other. In my "real life" the output usually gives NO RECORDS. as the source table is usually empty. Now imagine I have 48 columns to concatenate. With your solution it is +- 110 lines when it used to be 588 lines of code !!! Now important difference is possibility to select DISTINCT values for every column separately in old solution when in the new DISTINCT applies to all rows. But for me it is OK. Thanks a lot!
    – Look
    Jun 29 at 7:15
  • As I have so many fields I use this code below to help myself in writing this SELECT.
    – Look
    Jun 29 at 8:00
  • 1
    declare @source_table varchar(50) = '##table1' select ','','' + '+ case when t1.system_type_id in (167,231) then t1.name else 'CAST(A.'+t1.name+' as varchar) ' end + ' as '+ t1.name , ',STUFF(T.x.query('''+t1.name+'/text()'').value(''.'', ''varchar(max)'') ,1,1,'''') as '+t1.name from sys.dm_exec_describe_first_result_set('select * from '+@source_table,null,null) t1 where t1.name not in ('C1','C2') /*columns used in group by*/ order by t1.column_ordinal
    – Look
    Jun 29 at 8:09
  • I was working with both solutions for some time. Looks like this "nice" solution in Martin's answer is much slower for bigger tables. I decided to use it to make code more readable when a table is small. When table is big i use many subselect queries.
    – Look
    Aug 4 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.