我有以下查询：
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
