How do you create a query which returns dynamic column names in Postgresql?

Question

I have two tables in a reporting database, one for orders, and one for order items. Each order can have multiple order items, along with a quantity for each:

Orders
+----------+---------+
| order_id | email   |
+----------+---------+
| 1        | 1@1.com |
+----------+---------+
| 2        | 2@2.com |
+----------+---------+
| 3        | 3@3.com |
+----------+---------+

Order Items
+---------------+----------+----------+--------------+
| order_item_id | order_id | quantity | product_name |
+---------------+----------+----------+--------------+
| 1             | 1        | 1        | Tee Shirt    |
+---------------+----------+----------+--------------+
| 2             | 1        | 3        | Jeans        |
+---------------+----------+----------+--------------+
| 3             | 1        | 1        | Hat          |
+---------------+----------+----------+--------------+
| 4             | 2        | 2        | Tee Shirt    |
+---------------+----------+----------+--------------+
| 5             | 3        | 3        | Tee Shirt    |
+---------------+----------+----------+--------------+
| 6             | 3        | 1        | Jeans        |
+---------------+----------+----------+--------------+

For reporting purposes, I'd love to denormalise this data into a separate PostgreSQL view (or just run a query) that turns the data above into something like this:

+----------+---------+-----------+-------+-----+
| order_id | email   | Tee Shirt | Jeans | Hat |
+----------+---------+-----------+-------+-----+
| 1        | 1@1.com | 1         | 3     | 1   |
+----------+---------+-----------+-------+-----+
| 2        | 2@2.com | 2         | 0     | 0   |
+----------+---------+-----------+-------+-----+
| 3        | 3@3.com | 3         | 1     | 0   |
+----------+---------+-----------+-------+-----+

ie, it's a sum of the quantity of each item within the order with the product name; and the product names set as the column titles. Do I need to use something like crosstab to do this, or is there a clever way using subqueries even if I don't know the list of distinct product names at before the query runs.

if I don't know the list of distinct product names, use crosstab — Vao Tsun, Oct 12 '17 at 07:53
Not with a particularly nice query unfortunately, I had to list out each of my products in the query. Still looking for a good way of using crosstab for this — Joe, Dec 07 '17 at 16:29

score 0 · Answer 1 · edited Oct 12 '17 at 07:54

This is one possible answer:

create table orders 
(
  orders_id int PRIMARY KEY, 
  email text NOT NULL
);

create table orders_items 
(
  order_item_id int PRIMARY KEY, 
  orders_id int REFERENCES orders(orders_id) NOT NULL, 
  quantity int NOT NULL, 
  product_name text NOT NULL
);

insert into orders VALUES (1, '1@1.com');
insert into orders VALUES (2, '2@2.com');
insert into orders VALUES (3, '3@3.com');

insert into orders_items VALUES (1,1,1,'T-Shirt');
insert into orders_items VALUES (2,1,3,'Jeans');
insert into orders_items VALUES (3,1,1,'Hat');
insert into orders_items VALUES (4,2,2,'T-Shirt');
insert into orders_items VALUES (5,3,3,'T-Shirt');
insert into orders_items VALUES (6,3,1,'Jeans');


select 
  orders.orders_id, 
  email,
  COALESCE(tshirt.quantity, 0) as "T-Shirts",
  COALESCE(jeans.quantity,0) as "Jeans",
  COALESCE(hat.quantity, 0) as "Hats"
from 
  orders 
  left join (select orders_id, quantity from orders_items where product_name = 'T-Shirt') 
    as tshirt ON (tshirt.orders_id = orders.orders_id)
  left join (select orders_id, quantity from orders_items where product_name = 'Jeans') 
    as jeans ON (jeans.orders_id = orders.orders_id)
  left join (select orders_id, quantity from orders_items where product_name = 'Hat') 
    as hat ON (hat.orders_id = orders.orders_id)
;

Tested with postgresql. Result:

 orders_id |  email  | T-Shirts | Jeans | Hats 
-----------+---------+----------+-------+------
         1 | 1@1.com |        1 |     3 |    1
         2 | 2@2.com |        2 |     0 |    0
         3 | 3@3.com |        3 |     1 |    0
(3 rows)

Based on your comment, you can try to use tablefunc like this:

CREATE EXTENSION tablefunc;

SELECT * FROM crosstab
(
  'SELECT orders_id, product_name, quantity FROM orders_items ORDER BY 1',
  'SELECT DISTINCT product_name FROM orders_items ORDER BY 1'
)
AS
(
       orders_id text,
       TShirt text,
       Jeans text,
       Hat text
);

But I think you are thinking the wrong way about SQL. You usually know which rows you want and have to tell it SQL. "Rotating tables" 90 degrees is not part of SQL and should be avoided.

This is great, and certainly works. But what I'm really looking for is a query like this which will scale up to ~30 product names, where the product names are retrieved with a query like `select distinct(product_name) from order_items` — Joe, Oct 11 '17 at 22:54

How do you create a query which returns dynamic column names in Postgresql?

1 Answers1