如何在SQL PIVOT中为列实现“捕获所有其他值”的功能？

问题背景 #

需要统计指定日期（2023-04-30）下各游戏的有效玩家数（仅UserType=1算作有效玩家），并将结果按玩家数分组转换为单行多列的形式：

• 玩家数为0、1、2时分别对应0pGames、1pGames、2pGames列
• 玩家数为其他值时，全部汇总到AllOtherGames列

原查询先计算每个游戏的有效玩家数：

SELECT
    gm.[Id]
    ,SUM(CASE WHEN mbr.[UserType] = 1 THEN 1 ELSE 0 END) AS NbrPlayers
FROM
    dbo.[Game] gm
    LEFT JOIN dbo.[GameMembership] mbr ON gm.[Id] = mbr.[GameId]
WHERE
    CAST(gm.[DateCreated] AS DATE) = CAST('2023-04-30' AS DATE)
GROUP BY
    gm.[Id]

得到的示例结果：

Id                                      NbrPlayers
==================================================
FFA8C1D6-CEB6-4823-B436-18F0417EEBD0    2
E14B2558-F8B4-4660-967C-2EBC452BE881    0
208B46F8-B39D-4BC8-A907-57A07CD0A380    1
76275D64-AFD9-425C-8BD5-9FA770C24AA5    1
EA092967-680B-4EA6-A137-CD9834C51323    2
6C346BA1-1D3D-410A-A1A3-CF0B2F840C0E    1
EC68A93E-996F-4D4E-B443-DE63BD9B9F59    1
06C9A62D-9790-4080-8863-F7F2D0432F67    1
428D6C3F-57AF-4F6C-AE7F-F8C5D10AA6A5    2
DF027EE0-FAA3-4DA2-844A-F94AB6D56C2C    1
C82E60DF-7193-4099-B273-FE497B3002E5    1

使用PIVOT转换为指定列后：

SELECT
    [0] AS [0pGames], [1] AS [1pGames], [2] AS [2pGames]
FROM (
    SELECT
        gm.[Id]
        ,SUM(CASE WHEN mbr.[UserType] = 1 THEN 1 ELSE 0 END) AS NbrPlayers
    FROM
        dbo.[Game] gm
        LEFT JOIN dbo.[GameMembership] mbr ON gm.[Id] = mbr.[GameId]
    WHERE
        CAST(gm.[DateCreated] AS DATE) = CAST('2023-04-30' AS DATE)
    GROUP BY
        gm.[Id]
) x
PIVOT (
    COUNT([Id])
    FOR [NbrPlayers] IN ([0], [1], [2])
) pvt

得到结果：

0pGames    1pGames    2pGames
=============================
1          7          3

但PIVOT无法直接捕获未指定的NbrPlayers值，需要实现将其他值汇总到AllOtherGames列的需求。

解决方案 #

方法1：在子查询中统一标记非目标值后再PIVOT #

通过CASE语句将非0/1/2的玩家数统一标记为AllOther，再进行PIVOT操作：

SELECT
    [0] AS [0pGames], 
    [1] AS [1pGames], 
    [2] AS [2pGames],
    [AllOther] AS [AllOtherGames]
FROM (
    SELECT
        gm.[Id],
        -- 将非0/1/2的玩家数统一标记为AllOther
        CASE 
            WHEN SUM(CASE WHEN mbr.[UserType] = 1 THEN 1 ELSE 0 END) IN (0,1,2)
            THEN CAST(SUM(CASE WHEN mbr.[UserType] = 1 THEN 1 ELSE 0 END) AS VARCHAR(10))
            ELSE 'AllOther'
        END AS PlayerCategory
    FROM
        dbo.[Game] gm
        LEFT JOIN dbo.[GameMembership] mbr ON gm.[Id] = mbr.[GameId]
    WHERE
        CAST(gm.[DateCreated] AS DATE) = CAST('2023-04-30' AS DATE)
    GROUP BY
        gm.[Id]
) x
PIVOT (
    COUNT([Id])
    FOR [PlayerCategory] IN ([0], [1], [2], [AllOther])
) pvt

方法2：使用条件聚合替代PIVOT（推荐） #

直接通过外层的条件聚合统计各分类数量，逻辑更直观，无需依赖PIVOT的列限制：

SELECT
    SUM(CASE WHEN NbrPlayers = 0 THEN 1 ELSE 0 END) AS [0pGames],
    SUM(CASE WHEN NbrPlayers = 1 THEN 1 ELSE 0 END) AS [1pGames],
    SUM(CASE WHEN NbrPlayers = 2 THEN 1 ELSE 0 END) AS [2pGames],
    SUM(CASE WHEN NbrPlayers NOT IN (0,1,2) THEN 1 ELSE 0 END) AS [AllOtherGames]
FROM (
    SELECT
        gm.[Id],
        SUM(CASE WHEN mbr.[UserType] = 1 THEN 1 ELSE 0 END) AS NbrPlayers
    FROM
        dbo.[Game] gm
        LEFT JOIN dbo.[GameMembership] mbr ON gm.[Id] = mbr.[GameId]
    WHERE
        CAST(gm.[DateCreated] AS DATE) = CAST('2023-04-30' AS DATE)
    GROUP BY
        gm.[Id]
) x

两种方法都能得到目标结果：

0pGames    1pGames    2pGames    AllOtherGames
==============================================
1          7          3          4

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