如何一次性生成统计项为行、区域为列的SQL聚合统计结果？

问题背景 #

原始表结构 #

region    managed    sent        received    result
B01       0          9/4/2023    NULL        587320004
B02       1          7/31/2023   8/12/2023   587320002
B03       1          8/11/2023   9/5/2023    587320002
B04       0          9/1/2023    9/17/2023   587320001
B01       1          9/15/2023   9/16/2023   587320003
B01       0          8/12/2023   NULL        587320001
B02       0          6/28/2023   NULL        587320005
B04       1          7/15/2023   7/30/2023   587320004
B01       0          9/9/2023    9/12/2023   587320002

目标输出格式 #

需要将统计项作为行，区域作为列，输出如下格式：

B01   B02   B03   B04
# of records sent (managed = 1)
# of records sent last week (managed = 1)
# of records sent (managed = 0)
# of records sent last week (managed = 0)
# of records returned (received is NOT null)
# of records not returned (received is NULL)
# of records (result = 587320001 and managed = 0)
# of records (result = 587320001 and managed = 1)

当前查询问题 #

用户已写出如下查询，能获取所需统计数据，但格式不符合要求（以region/managed组合为行，统计项为列）：

select region, managed,
count(case when sent >= @StartLastWeek then 1 else null) as 'Sent Last Week',
count(*) as 'Sent All Time',
count(case when received is not NULL then 1 else null) as 'Received',
count(case when received is NULL then 1 else null) as 'Not Received',
count(case when result = 587320001 then 1 else null) as 'Positive',
count(case when result = 587320002 then 1 else null) as 'Negative'
from mytable
group by region, managed

需实现无需UNION或CTE的一次性查询，得到目标格式。

解决方案 #

可以通过构造统计项标识行，结合条件聚合实现区域列的统计，具体SQL如下：

SELECT
    stat_item,
    COUNT(CASE WHEN region = 'B01' AND condition THEN 1 END) AS B01,
    COUNT(CASE WHEN region = 'B02' AND condition THEN 1 END) AS B02,
    COUNT(CASE WHEN region = 'B03' AND condition THEN 1 END) AS B03,
    COUNT(CASE WHEN region = 'B04' AND condition THEN 1 END) AS B04
FROM (
    SELECT
        region,
        managed,
        sent,
        received,
        result,
        stat_item
    FROM mytable
    CROSS JOIN (
        VALUES
            ('# of records sent (managed = 1)'),
            ('# of records sent last week (managed = 1)'),
            ('# of records sent (managed = 0)'),
            ('# of records sent last week (managed = 0)'),
            ('# of records returned (received is NOT null)'),
            ('# of records not returned (received is NULL)'),
            ('# of records (result = 587320001 and managed = 0)'),
            ('# of records (result = 587320001 and managed = 1)')
    ) AS stats(stat_item)
) AS t
CROSS APPLY (
    SELECT
        CASE stat_item
            WHEN '# of records sent (managed = 1)' THEN managed = 1
            WHEN '# of records sent last week (managed = 1)' THEN managed = 1 AND sent >= @StartLastWeek
            WHEN '# of records sent (managed = 0)' THEN managed = 0
            WHEN '# of records sent last week (managed = 0)' THEN managed = 0 AND sent >= @StartLastWeek
            WHEN '# of records returned (received is NOT null)' THEN received IS NOT NULL
            WHEN '# of records not returned (received is NULL)' THEN received IS NULL
            WHEN '# of records (result = 587320001 and managed = 0)' THEN result = 587320001 AND managed = 0
            WHEN '# of records (result = 587320001 and managed = 1)' THEN result = 587320001 AND managed = 1
        END AS condition
) AS cond
GROUP BY stat_item
ORDER BY
    CASE stat_item
        WHEN '# of records sent (managed = 1)' THEN 1
        WHEN '# of records sent last week (managed = 1)' THEN 2
        WHEN '# of records sent (managed = 0)' THEN 3
        WHEN '# of records sent last week (managed = 0)' THEN 4
        WHEN '# of records returned (received is NOT null)' THEN 5
        WHEN '# of records not returned (received is NULL)' THEN 6
        WHEN '# of records (result = 587320001 and managed = 0)' THEN 7
        WHEN '# of records (result = 587320001 and managed = 1)' THEN 8
    END

关键逻辑说明 #

1. 生成统计项列表：用CROSS JOIN VALUES生成所有需要的统计项，与原表做笛卡尔积，确保每个统计项能遍历全表数据。
2. 绑定条件判断：通过CROSS APPLY根据统计项名称生成对应的过滤条件，简化后续聚合逻辑。
3. 区域列统计：针对每个区域，用COUNT(CASE ...)统计符合当前统计项条件的记录数，实现列转行。
4. 排序控制：通过ORDER BY指定统计项的显示顺序，与需求格式完全匹配。

该方案未使用UNION或CTE，仅通过一次查询实现需求，适合数万行数据的场景（若区域数量动态变化，需改用动态SQL，本方案适用于固定区域B01-B04的情况）。

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