Create Pivot Table using PostgreSQL

Question

I have a table like this:

type    code    desc    store  Sales/Day  Stock
-----------------------------------------------
1       AA1     abc     101        3        6
1       AA2     abd     101        4        0
1       AA3     abf     101        4        3
2       BA1     bba     101        5        1
2       BA2     bbc     101        2        1
1       AA1     abc     102        1        4
1       AA2     abd     102        2        0
2       BA1     bba     102        4        2
2       BA2     bbc     102        5        5
etc.

How I can show the result table like this:

type    code    desc         Store 101             Store 102
                        Sales/Day | Stock    Sales/Day | Stock
--------------------------------------------------------------
1       AA1     abc        3          6         1          4
1       AA2     abd        4          0         2          0
1       AA3     abf        4          3         0          0
2       BA1     bba        5          1         4          2
2       BA2     bbc        2          1         5          5
etc.

Note: Colspan is only display.

Why not using some reporting like `Jasper Report` or `Crystal Report`? It would be much easier rather than do all in query.. — dwir182, Nov 01 '18 at 07:18
https://stackoverflow.com/questions/3002499/postgresql-crosstab-query — sibert, Nov 01 '18 at 07:27
@dwir182: User want to show the result in excel and send it with automatic email every day. So I create with query and get the data. -sibert: Thanks, i will learn. — Fadhel J Muhammad, Nov 01 '18 at 07:42

S-Man · Accepted Answer · 2018-11-01T10:15:06.173

0

demo:db<>fiddle

First way: FILTER

SELECT
    type,
    code,
    "desc",
    COALESCE(SUM(sales_day) FILTER (WHERE store = 101)) as sales_day_101,
    COALESCE(SUM(stock) FILTER (WHERE store = 101), 0) as stock_101,
    COALESCE(SUM(sales_day) FILTER (WHERE store = 102), 0) as sales_day_102,
    COALESCE(SUM(stock) FILTER (WHERE store = 102), 0) as stock_102
FROM mytable
GROUP BY type, code, "desc"
ORDER BY type, code

Aggregating your values. I took SUM but in your case with distinct rows many other aggregate functions would do it. FILTER allows you to aggregate only one store.

The COALESCE is to avoid NULL values if no values are present for one aggregation (like AA3 in store 102).

Second way, CASE WHEN

SELECT
    type,
    code,
    "desc",
    SUM(CASE WHEN store = 101 THEN sales_day ELSE 0 END) as sales_day_101,
    SUM(CASE WHEN store = 101 THEN stock ELSE 0 END) as stock_101,
    SUM(CASE WHEN store = 102 THEN sales_day ELSE 0 END) as sales_day_102,
    SUM(CASE WHEN store = 102 THEN stock ELSE 0 END) as stock_102
FROM mytable
GROUP BY type, code, "desc"
ORDER BY type, code

The idea is the same, but the newer FILTER function is replace by the more common CASE clause.

Notice that "desc" is a reserved word in Postgres. So I strictly recommend to rename your column.

edited Nov 01 '18 at 10:15

answered Nov 01 '18 at 07:42

S-Man

22,521
7
40
63

I think op want pivot.. See there are `store 101` and `store 102` in top.. – dwir182 Nov 01 '18 at 07:44
See my demo. This is common way to pivot table. It is exactly your expected result – S-Man Nov 01 '18 at 07:48
Thanks, it will be the second choice. Because, if there is a new store, i have to change the query again. – Fadhel J Muhammad Nov 01 '18 at 07:53
Yes in both cases you'll have to change the query. In the first you have to add 2 FILTER lines, in the second one you'll need 2 new CASE lines. – S-Man Nov 01 '18 at 07:57
@S-Man Ok, I will remember. Thanks – Fadhel J Muhammad Nov 01 '18 at 08:31
@FadhelJMuhammad Thanks for accepting. Please do not forget to upvote as well (accepting shows that your problem is solved, upvoting is for the internal statistics of the respondent) – S-Man Nov 02 '18 at 06:38

Create Pivot Table using PostgreSQL

1 Answers1