string_agg for sql server pre 2017

问题:

Can anyone help me make this query work for sql server 2014. This is working on Postgresql and probably on sql server 2017. On Oracle it is listagg instead of string_agg.

Here is the sql:

select 
string_agg(t.id,',') AS id
from Tabel t

I checked on the site some xml option should be used but I could not understand it.

回答1:

In SQL Server pre-2017, you can do:

select stuff( (select ',' + cast(t.id as varchar(max))
               from tabel t
               for xml path ('')
              ), 1, 1, ''
            );

The only purpose of stuff() is to remove the initial comma. The work is being done by for xml path.