我有以下查询：
- SELECT
- Account,Unit,SUM(state_fee),Code
- FROM tblMta
- WHERE MTA.Id = '123'
- GROUP BY Account,Unit
这当然会引发异常,因为代码不在group by子句中.每个state_fee都有一个代码.如何将此代码显示为1个记录(每个state_fee为1个代码,单位为多个state_fee),以逗号分隔的列表？我在这里考察了不同的解决方案,但是我找不到与一个团队合作的任何方法.
解决方法
您要使用FOR XML PATH构造：
- SELECT ACCOUNT,unit,Stuff((SELECT ',' + code
- FROM tblmta t2
- WHERE t2.ACCOUNT = t1.ACCOUNT
- AND t2.unit = t1.unit
- AND t2.id = '123'
- FOR XML PATH('')),1,2,'') [Codes]
- FROM tblmta t1
- WHERE t1.id = '123'
- GROUP BY ACCOUNT,unit
请参阅其他示例：
> SQL same unit between two tables needs order numbers in 1 cell
> SQL Query to get aggregated result in comma seperators along with group by column in SQL Server
