SQL Server问题：如何按账号及服务ID合并多行数据？

我在使用SQL Server时，想要将同一账号下相同服务ID的多行数据合并为一行，但使用GROUP BY时，SQL要求对所有列进行分组，导致结果和原数据一致，无法实现合并。

当前表结构 #

+-----------------------------------------------------------------------------------------------------+
| service_id | account    | Aug Aide Billed | Aug Trans Billed | Sept Aide Billed | Sept Trans Billed | 
|    001A    | John Smith |        1        |         0        |          0       |         0         | 
|    001A    | John Smith |        0        |         1        |          0       |         0         |
|    001A    | John Smith |        0        |         0        |          1       |         0         |
|    001A    | John Smith |        0        |         0        |          0       |         1         |
|    001B    | John Smith |        1        |         0        |          0       |         0         | 
|    001B    | John Smith |        0        |         1        |          0       |         0         |
|    001B    | John Smith |        0        |         0        |          1       |         0         |
|    001B    | John Smith |        0        |         0        |          0       |         1         |
+-----------------------------------------------------------------------------------------------------+

期望结果 #

+-----------------------------------------------------------------------------------------------------+
| service_id | account    | Aug Aide Billed | Aug Trans Billed | Sept Aide Billed | Sept Trans Billed | 
|    001A    | John Smith |        1        |         1        |          1       |         1         | 
|    001B    | John Smith |        1        |         1        |          1       |         1         | 
+-----------------------------------------------------------------------------------------------------+

原查询语句 #

select service_id, account,
case when (date_from between '2021-08-01' and '2021-08-31') and posted_flag = 'y' and service_id = '001A' then 1 else 0 end as 'Aug Aide Billed',
case when (date_from between '2021-08-01' and '2021-08-31') and posted_flag = 'y' and service_id in ('001B', '001C') then 1 else 0 end as 'Aug Trans Billed',
case when (date_from between '2021-09-01' and '2021-09-30') and posted_flag = 'y' and service_id = '001A' then 1 else 0 end as 'Sept Aide Billed',
case when (date_from between '2021-09-01' and '2021-09-30') and posted_flag = 'y' and service_id in ('001B', '001C') then 1 else 0 end as 'Sept Trans Billed',
from table1 r
join table2 p
on r.id = p.id
where p.account = 'John Smith'
and r.service_id like '001%'
and date_from between DATEFROMPARTS('2021',8,1) and DATEFROMPARTS('2021',9,31)

我尝试过按account或service_id进行GROUP BY，但SQL要求对所有其他列分组，导致结果没有变化，无法实现合并。

解决方案 #

问题出在没有对CASE生成的列使用聚合函数，直接GROUP BY会因为每行的CASE结果不同而无法合并。我们可以用MAX()函数（因为目标是只要有一行满足条件就标记为1，MAX能取到分组内的最大值1）聚合这些标记列，同时仅按service_id和account分组即可。

修改后的查询语句 #

SELECT 
    r.service_id, 
    p.account,
    MAX(CASE WHEN (date_from BETWEEN '2021-08-01' AND '2021-08-31') AND posted_flag = 'y' AND r.service_id = '001A' THEN 1 ELSE 0 END) AS 'Aug Aide Billed',
    MAX(CASE WHEN (date_from BETWEEN '2021-08-01' AND '2021-08-31') AND posted_flag = 'y' AND r.service_id IN ('001B', '001C') THEN 1 ELSE 0 END) AS 'Aug Trans Billed',
    MAX(CASE WHEN (date_from BETWEEN '2021-09-01' AND '2021-09-30') AND posted_flag = 'y' AND r.service_id = '001A' THEN 1 ELSE 0 END) AS 'Sept Aide Billed',
    MAX(CASE WHEN (date_from BETWEEN '2021-09-01' AND '2021-09-30') AND posted_flag = 'y' AND r.service_id IN ('001B', '001C') THEN 1 ELSE 0 END) AS 'Sept Trans Billed'
FROM table1 r
JOIN table2 p ON r.id = p.id
WHERE 
    p.account = 'John Smith'
    AND r.service_id LIKE '001%'
    AND date_from BETWEEN DATEFROMPARTS(2021,8,1) AND DATEFROMPARTS(2021,9,30) -- 修正9月31日的无效日期错误
GROUP BY r.service_id, p.account;

关键说明 #

• MAX()函数会在同一service_id和account分组内，对每个CASE列取最大值：只要分组内有一行满足条件返回1，最终结果就会保留1，实现合并后的标记效果。
• GROUP BY仅需指定分组依据service_id和account，其他列通过聚合函数计算，无需加入GROUP BY。
• 修正了原语句中DATEFROMPARTS('2021',9,31)的错误，9月没有31号，该日期会导致SQL报错，改为DATEFROMPARTS(2021,9,30)。

内容的提问来源于stack exchange，提问作者Matt S