带 Group By 子句的 SQL 逗号分隔行

Question

我有以下查询:

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)？我在这里研究了不同的解决方案，但找不到任何适用于 group by 的解决方案。

Answer 1

您想要使用 FOR XML PATH 构造:

SELECT ACCOUNT, 
       unit, 
       SUM(state_fee), 
       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