How to pivot in postgresql

Question

I have table like following,and I would like to transform them.

year month week type count
2021  1     1    A    5
2021  1     1    B    6
2021  1     1    C    7
2021  1     2    A    0
2021  1     2    B    8
2021  1     2    C    9

I'd like to pivot like following.

year month week  A  B  C
2021  1     1    5  6  7
2021  1     2    0  8  9

I tried like following statement, but it returned a lot of null columns. And I wonder I must add columns one by one when new type will be added.

select
    year,
    month,
    week,
    case when type in ('A') then count end as A,
    case when type in ('B') then count end as B,
    case when type in ('C') then count end as C,
    
from
   table

If someone has opinion, please let me know. Thanks

Earlier related questions: https://stackoverflow.com/q/29020065/939860 or https://stackoverflow.com/q/3002499/939860 or https://stackoverflow.com/q/16598112/939860 — Erwin Brandstetter, May 05 '23 at 01:35

S-Man · Accepted Answer · 2021-09-21T06:07:20.683

demo: db<>fiddle

You can either use the FILTER clause:

SELECT
    year, month, week,
    MAX("count") FILTER (WHERE type = 'A') as A,   -- 2
    MAX("count") FILTER (WHERE type = 'B') as B,
    MAX("count") FILTER (WHERE type = 'C') as C
FROM mytable
GROUP BY year, month, week                         -- 1
ORDER BY year, month, week

or you can use the CASE clause:

SELECT
    year, month, week,
    MAX (CASE WHEN type = 'A' THEN "count" END) AS A,
    MAX (CASE WHEN type = 'B' THEN "count" END) AS B,
    MAX (CASE WHEN type = 'C' THEN "count" END) AS C
FROM mytable
GROUP BY year, month, week
ORDER BY year, month, week

In both cases you need to perform a GROUP BY action.
This makes an aggregation function necessary, like MAX() or SUM(). Finally you need to apply a kind of filter (CASE or FILTER) to only aggregate the related data.

Additionally: Please note that the words count, year, month, week are keywords of SQL. To avoid any complications you should think about other column names.

I think that the `crosstab` function would serve the purpose. Maybe worth checking that out https://www.postgresql.org/docs/14/tablefunc.html — IvanP, Feb 04 '22 at 11:58
@IvanP you could, but the example here with the `FILTER` seems to work fine and you don't have your query as a string. It just seems simpler than the crosstab function. Can you explain the difference in terms of performance? — Michael Z., Aug 09 '22 at 20:37

L. Rodgers · Answer 2 · 2022-09-22T12:02:33.083

This question has been asked many times, & there are decent (even dynamic) solutions. While CROSSTAB() is available in recent versions of Postgres, not everyone has sufficient user privileges to install the prerequisite extension.

One such solution involves a temp type (temp table) created by an anonymous function & JSON expansion of the resultant type.

See also: DB FIDDLE (UK): https://dbfiddle.uk/Sn7iO4zL

How to pivot or crosstab in postgresql without writing a function?

How to pivot in postgresql

2 Answers2

Linked

Related