PostgreSQL Crosstab Query With Changing Rows

Question

Can someone please help me put this query together?

I have this table:

store name       status          orders
billys store     new             15
billys store     ordered         20
billys store     canceled        2
johnny store     new             5
johnny store     out_of_stock    20
rosie store      new             6
rosie store      ordered         4
rosie store      out_of_stock    10

So as you can see, some stores have some statuses that others don't.

My desired result is the following:

store name      new       ordered      canceled      out of stock
billys store    15        20           2             0
johnny store    5         0            0             20
rosie store     6         4            0             10

I have tried the following:

SELECT * FROM crosstab(
    'SELECT store_name::text as store_name, 
            status::text as status, 
            count(*)::int as orders
     FROM organizations
     INNER JOIN orders ON organization_id = organizations.id
     GROUP BY store_name, status
     ORDER BY store_name, status'
) x (store_name text, "new" int, "ordered" int)

But this doesn't work since it will break when the new row is not an expected value. For example with 'johnny store', after 'new' is not 'ordered', it's 'out_of_stock' so that won't work.

I've looked through a bunch of StackOverflow posts but I'm just overall pretty confused. Thank you

Did you check this one? https://stackoverflow.com/questions/28087948/dynamic-pivot-query-using-postgresql-9-3 — Cetin Basoz, Feb 24 '22 at 18:03

score 0 · Accepted Answer · 2022-02-24T17:51:08.940

0

We can do this using CASE to avoid using sub-queries.

CREATE TABLE organisation  (
  store_name VARCHAR(25),  
  status VARCHAR(25),
  orders INT);
INSERT INTO organisation VALUES
('billys store',     'new'         ,    15),
('billys store',     'ordered'     ,    20),
('billys store',     'canceled'    ,    2),
('johnny store',     'new'         ,    5),
('johnny store',     'out_of_stock',    20),
('rosie store' ,     'new'         ,    6),
('rosie store' ,     'ordered'     ,    4),
('rosie store' ,     'out_of_stock',    10);

8 rows affected

SELECT store_name,
   SUM(CASE WHEN status='new' THEN orders ELSE 0 END) new_,
SUM(CASE WHEN status='canceled' THEN orders ELSE 0 END) canceled,
SUM(CASE WHEN status='ordered' THEN orders ELSE 0 END) ordered,
SUM(CASE WHEN status='new' THEN orders ELSE 0 END) o_o_s
FROM organisation o
GROUP BY store_name; 
GO

store_name   | new | canceled | ordered | o_o_s
:----------- | --: | -------: | ------: | ----:
billys store |  15 |        2 |      20 |    15
johnny store |   5 |        0 |       0 |     5
rosie store  |   6 |        0 |       4 |     6

db<>fiddle here

edited Feb 24 '22 at 17:51

answered Feb 24 '22 at 17:45

GO in postgreSQL??? Also OP is using tablefunc extension. – Cetin Basoz Feb 24 '22 at 17:48
@Cetin Basoz - Thank you for the comment. I'd forgotten to change the setting in DBfiddle. It works ok in Postgre, I just needed to add a character to column name "new" -> "new_" to avoid keyword problem. When we stay in standard SQL in I try to write platform independent queries. – Feb 24 '22 at 17:55
OK but it doesn't answer OP question, does it? (even after correcting the code?) What OP describes is dynamic pivot (or crosstab if you wish), he doesn't want to hardcode, and with tablefunc extension it is much easier than how it is in SQL server (or standard SQL as you called it - that operation is standard in name only I think). – Cetin Basoz Feb 24 '22 at 18:00
As I understood it the problem was that if a store had no entry for a status his query wasn't working as designed. We've solved that with the "ELSE 0". – Feb 24 '22 at 18:03
OK our perception of the question is different then. We are reading the question and postgreSQL code sample provided different. It would be super simple as if it were as you said. – Cetin Basoz Feb 24 '22 at 18:06
@Cetin I've tried to follow the logic and my result set matches what is asked for. Walking is that ok for you or could you explain please explain a little more? Thank you – Feb 24 '22 at 18:09
@CetinBasoz do you have a better solution than the above? – Walking Feb 24 '22 at 19:26
@Walking, yes check the link I provided in my comment above. If it is all you needed, you could have included it in your columns list, or use CrossTab3(...). – Cetin Basoz Feb 24 '22 at 20:25

score 0 · Answer 2 · answered Feb 24 '22 at 20:53

Maybe you couldn't understand it from the link I provided but tablefunc extension makes this much easier IMHO. Here is a sample based on your code, you would replace the first query with yours that gets the data from your tables:

create temporary table myTable (storename text, status text, orders int);
insert into myTable (storename, status, orders)
values
('billys store','new', 15),
('billys store','ordered', 20),
('billys store','canceled', 2),
('johnny store','new', 5),
('johnny store','out_of_stock', 20),
('rosie store','new', 6),
('rosie store','ordered', 4),
('rosie store','out_of_stock', 10);


SELECT * FROM crosstab(
    'SELECT storename,
            status,
            orders
     FROM myTable',
    'select * from unnest(string_to_array(''new,ordered,canceled,out_of_stock'', '',''))'
) x (storename text, "new" int, "ordered" int, "canceled" int, "out_of_stock" int);

drop table  myTable;

Here is DBFiddle demo

PostgreSQL Crosstab Query With Changing Rows

2 Answers2