String_agg equivalent function in SQL server

Question

I want to rewrite my SQL query with stuff or other option available instead of string_agg function as my SQL server doesn't support it. Can anyone please help me with this?

select String_agg(air.code,',') AS Code,String_agg(air.Id,',') AS AId,res.ResId
from  Table1 air
inner join
Table2 rmap on air.Id=airmap.Id
inner join Table3 res on rmap.ResId=res.ResId
group by res.ResId

with output like below

Please provide sample data and desired results. Also, the functionality is not `stuff()`, which is a simple string function. The functionality is `for xml path`. — Gordon Linoff, Sep 15 '20 at 13:29
`STUFF` doesn't aggregate anything, it only removes the leading separator. It's `FOR XML` that aggregates the values as XML whose element names are empty — Panagiotis Kanavos, Sep 15 '20 at 13:30
`STRING_AGG` is available as of SQL Server 2017. If you have an older version, you may want to consider an upgrade. — Thorsten Kettner, Sep 15 '20 at 13:39

score 1 · Answer 1 · answered Sep 15 '20 at 13:31

Presumably, you want something like this:

select res.ResId, 
       stuff( (select ',' + air.code
               from table1 air inner join
                    Table2 rmap
                    on air.Id = airmap.Id
               where rmap.ResId = res.ResId
               for xml path ('')
              ), 1, 1, '') as codes,
       stuff( (select ',' + air.aid
               from table1 air inner join
                    Table2 rmap
                    on air.Id = airmap.Id
               where rmap.ResId = res.ResId
               for xml path ('')
              ), 1, 1, '') as aids
from Table3 res ;

At times like that, SQLCLR looks like a far better option. Or upgrading to a recent SQL Server version — Panagiotis Kanavos, Sep 15 '20 at 13:32

String_agg equivalent function in SQL server

1 Answers1