How to create a query that returns dynamic column names in Postgresql?

Question

I have two tables in the report database, one for orders and one for order items. Each order may have several order items along with a quantity for each:

Orders +----------+---------+ | order_id | email | +----------+---------+ | 1 | [e-mail] | +----------+---------+ | 2 | [e-mail] | +----------+---------+ | 3 | [e-mail] | +----------+---------+ 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 would like to denormalize this data in a separate PostgreSQL view (or just run the query), which turns the data above into something like this:

 +----------+---------+-----------+-------+-----+ | order_id | email | Tee Shirt | Jeans | Hat | +----------+---------+-----------+-------+-----+ | 1 | [e-mail] | 1 | 3 | 1 | +----------+---------+-----------+-------+-----+ | 2 | [e-mail] | 2 | 0 | 0 | +----------+---------+-----------+-------+-----+ | 3 | [e-mail] | 3 | 1 | 0 | +----------+---------+-----------+-------+-----+ 

those. This is the sum of the quantity of each item in the order with the product name; and product names defined as column headers. Do I need to use something like a crosstab to do this, or is there a reasonable way to use subqueries, even if I don't know the list of individual product names before the query is executed.

Answer 1

This is one of the possible answers:

 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, ' [e-mail] '); insert into orders VALUES (2, ' [e-mail] '); insert into orders VALUES (3, ' [e-mail] '); 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 | [e-mail] | 1 | 3 | 1 2 | [e-mail] | 2 | 0 | 0 3 | [e-mail] | 3 | 1 | 0 (3 rows) 

Based on your comment, you can try using tablefunc as follows:

 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 wrong about SQL. Usually you know what rows you want, and you must say this SQL. 90-degree rotating tables are not part of SQL and should be avoided.