sqlsql-serverstring-aggregationstring-agg

String_agg for SQL Server before 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 
    Table t

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

Solution

  • 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.