Create Postgresql Crosstab Query with multiple categories

Question

So far I have created a query that can give me the following result:

+------------+----------+-------------+-------+
|    date    | category | subcategory | count |
+------------+----------+-------------+-------+
| 2020-04-23 | One      | First       |     1 |
| 2020-04-23 | Two      | Second      |     1 |
| 2020-04-23 | Two      | First       |     3 |
| 2020-04-23 | Three    | Third       |     3 |
| 2020-04-23 | Three    | Second      |     1 |
| 2020-04-23 | Four     | Second      |     2 |
| 2020-04-23 | Five     | Third       |     3 |
| 2020-04-23 | Five     | Second      |     1 |
| 2020-04-23 | Five     | First       |     1 |
| 2020-04-23 | Six      | Third       |     1 |
| 2020-04-23 | Six      | Second      |     2 |
+------------+----------+-------------+-------+

I would like to turn it into the following but I can't figure it out:

+------------+----------+-------+--------+-------+
|    date    | category | First | Second | Third |
+------------+----------+-------+--------+-------+
| 2020-04-23 | One      |     1 |      0 |     0 |
| 2020-04-23 | Two      |     2 |      3 |     0 |
| 2020-04-23 | Three    |     0 |      1 |     3 |
| 2020-04-23 | Five     |     1 |      2 |     3 |
| 2020-04-23 | Six      |     0 |      2 |     1 |
+------------+----------+-------+--------+-------+

I tried the following, but it looks like you have to return a row, column, and value when using crosstab so it doesn't work:

SELECT *
FROM crosstab(
    $$
        SELECT date, category, subcategory, count(*)
        -- ...
        GROUP BY 1, 2, 3
    $$
)
AS ct(date date, category text, First int, Second int, Third int);

Is there a way to use multiple values for the row indicator when using crosstab, or will I have to find some other approach?

GammaGames · Accepted Answer · 2020-04-27T17:24:35.057

I found a solution, though it's not ideal: merge the first two with a unique character, get the crosstab query with a CTE, and split the resulting columns. It looks something like:

WITH crosstb AS (
    SELECT *
    FROM crosstab(
        $$
        -- Concatenate date and category columns
        SELECT date || '_' || category, subcategory, count(*)
        -- ...
        GROUP BY 1, 2
        $$,
        -- Specify data columns to ensure null-count rows are included
        $$VALUES ('First'), ('Second'), ('Third')$$
    )
    AS ct(datecat text, First numeric, Second numeric, Third numeric)
)
SELECT 
    -- Split datecat column into separate again
    (regexp_split_to_array(datecat, '_'))[1]::date as Date, 
    (regexp_split_to_array(datecat, '_'))[2] as category, 
    COALESCE(First, 0),
    COALESCE(Second, 0),
    COALESCE(Third, 0)
FROM crosstb;

Create Postgresql Crosstab Query with multiple categories

1 Answers1