Postgresql crosstab on multi pivoting and multi columns

Question

I’ve been trying to crosstab on multi pivots and multi columns

How do I go from

create table sales(year int, branch text, month int, qty int, scrap int, yield int);
insert into sales values(2007, 'Houston', 1, 1000, 66, 11);
insert into sales values(2007, 'Houston', 3, 1500, 55, 22);
insert into sales values(2007, 'Austin',  3,  500, 44, 33);
insert into sales values(2007, 'Laredo',  2, 1500, 77, 44);
insert into sales values(2007, 'El Paso', 2, 2000, 88, 55);
insert into sales values(2008, 'Waco',    1,  900, 99, 66);

to crosstab like this

 year | branch  | jan_qty | jan_scr | jan_yld | feb_qty | feb_scr | feb_yld | mar_qty | mar_scr | mar_yld
------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
 2007 | Austin  |         |         |         |         |         |         |     500 |      44 |      33     
 2007 | El Paso |         |         |         |    2000 |      88 |      55 |         |         |     
 2007 | Houston |    1000 |      66 |      11 |         |         |         |    1500 |      55 |      22
 2007 | Laredo  |         |         |         |    1500 |      77 |      44 |         |         | 
 2008 | Waco    |     900 |      99 |      66 |         |         |         |         |         |

where both year and branch are pivots and each month contains 3 values?

Matthieu Libeer · Answer 1 · 2018-09-05T23:59:16.243

1. The easiest route

Is probably the following. It's not DRY, but it does the job, it's readable, works for any SQL flavor, and it can be easily generated by application code.

SELECT
  grain.year,
  grain.branch,
  (SELECT qty FROM sales WHERE sales.year = grain.year AND sales.branch = grain.branch AND sales.month = 1) AS jan_qty,
  (SELECT scr FROM sales WHERE sales.year = grain.year AND sales.branch = grain.branch AND sales.month = 1) AS jan_scr,
  (SELECT yld FROM sales WHERE sales.year = grain.year AND sales.branch = grain.branch AND sales.month = 1) AS jan_yld,
  ... same for feb ...,
  ... same for mar ...
FROM (SELECT DISTINCT year, branch FROM sales) grain

Application code just needs the names of the columns jan_qty, jan_scr ... to generate this SQL with a simple loop.

I would make sure to EXPLAIN ANALYZE the query above to check that PostgreSQL runs this query efficiently.

2. The beautiful route

This is a pivot table, and PostgreSQL gives us the CROSSTAB function for this kind of query. This post perfectly explains how to do so, including how to deal with missing values (i.e. empty cells in the result table), which is the case in the example you provided

tanks @Matthieu, being honest, I had already seen the link that you give me but I couldn't have several columns for the same month or several pivots. I ended up doing it manually with something similar to your easiest route. I'm still without crosstab — glicuado, Sep 13 '18 at 03:32

Postgresql crosstab on multi pivoting and multi columns

1 Answers1