How to "catch all other values" FOR a column in a SQL PIVOT?

May 2, 2023

I have a SQL query where I’m calculating the number of players for a game based on UserType (only 1 counts as an actual player for the purposes of my calculation), and listing this info out for all games on a certain date:

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]

This gives the result, for my sample data:

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

However, what I then want to do is turn this into a single row of output, with columns for each distinct NbrPlayers value. As I know that I have 0, 1, and 2 in this output, I can do this with PIVOT like so:

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

Which results in:

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

This problem is, what happens when I get other NbrPlayers values, and I don’t necessarily know in advance what they might be? What’s more, I actually don’t want a separate column for each one of those other values; I want one column that sums up "everything else". Say I had a couple of entries for NbrPlayers being 3, and a couple where NbrPlayers was 7. I’d want an output like:

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

As far as I can tell, PIVOT doesn’t have this "catch-all" functionality that can be specified in the FOR clause, so how can I achieve this?

>Solution :

You can do the pivot manually, in other words use conditional aggregation

SELECT
  [0pGames] = COUNT(CASE WHEN NbrPlayers = 0 THEN 1 END),
  [1pGames] = COUNT(CASE WHEN NbrPlayers = 1 THEN 1 END),
  [2pGames] = COUNT(CASE WHEN NbrPlayers = 2 THEN 1 END),
  AllOtherGames = COUNT(CASE WHEN NbrPlayers NOT IN (0, 1, 2) THEN 1 END)
FROM (
    SELECT
      gm.Id,
      COUNT(CASE WHEN mbr.UserType = 1 THEN 1 END) AS NbrPlayers
    FROM dbo.Game gm
    LEFT JOIN dbo.GameMembership mbr ON gm.Id = mbr.GameId
    WHERE gm.DateCreated >= '20230430'
      AND gm.DateCreated < '20230501' 
    GROUP BY
      gm.Id
) gm;

Note the use of a date range, rather than casting to date which is inefficient.

Also, your main query seems questionable. You are using a left-join, but then filtering in the COUNT. You could equally filter inside the left-join, then just count the number of matches.

SELECT
  [0pGames] = COUNT(CASE WHEN NbrPlayers = 0 THEN 1 END),
  [1pGames] = COUNT(CASE WHEN NbrPlayers = 1 THEN 1 END),
  [2pGames] = COUNT(CASE WHEN NbrPlayers = 2 THEN 1 END),
  AllOtherGames = COUNT(CASE WHEN NbrPlayers NOT IN (0, 1, 2) THEN 1 END)
FROM (
    SELECT
      gm.Id,
      COUNT(mbr.GameId) AS NbrPlayers
    FROM dbo.Game gm
    LEFT JOIN dbo.GameMembership mbr ON gm.Id = mbr.GameId
        AND mbr.UserType = 1
    WHERE gm.DateCreated >= '20230430'
      AND gm.DateCreated < '20230501' 
    GROUP BY
      gm.Id
) gm;