sql - SQL server 中的 String_agg 等效函数

Question

我想用可用的东西或其他选项而不是 string_agg 函数重写我的 SQL 查询，因为我的 SQL 服务器不支持它。谁能帮我解决这个问题？

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

输出如下

标签： sqlsql-server

Answer 1

大概，你想要这样的东西：

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 ;