带Group By子句的SQL逗号分隔行 [英] SQL comma-separated row with Group By clause

问题描述

我有以下查询：

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 一起工作的任何解决方案。

This of course throws an exception because the Code is not in the group by clause. Each state_fee has a code. How do I get this code to display in 1 record (1 code per state_fee which is multiple state_fee per unit) as a comma-separated list? I looked into different solutions on here but I couldn't find any that worked with a group by.

推荐答案

您要使用 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相同单元需要1个单元格中的订单号


• SQL查询以逗号分隔符获取汇总结果以及SQL Server中的按列分组

• 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

这篇关于带Group By子句的SQL逗号分隔行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！