PostgreSQL Crosstab Query With Changing Rows-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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