How do you create cross-tab (pivot) queries in SQL
Crosstabs in postgres are a little funky. You are basically passing two strings into a function. I am using dollar sign quoting to make it more readable. Everything between the two sets of $$ is a string.
The first string is a query that returns 3 columns. The first column is the row ID and will act like a row label in an Excel pivot table. The second column will act like the column label in an Excel pivot table. The third column is the field you would drop into Values in an Excel pivot.
The second string maps the values to the buckets. It is important to make sure that the order you put these in is the same order you use when naming the fields.
select * from crosstab($$ select -- there can only be 3 items in the select inside a crosstab. ib.item_number, -- the first line is the row name ib.warehouse, -- the second line is the bucket ib.quantity_on_hand -- and the third line is the bucket value from amflib.m75_item_balance_file ib where ib.warehouse in ('5', '97T') order by 1,2$$, -- do not forget to include this order by. $$values ('5'),('97T')$$ -- here you have to list the possible values for warehouse ) as ct (item_nbr varchar(15), warehouse_5 numeric, warehouse_97t numeric) -- here you have to list the final column names followed by the type of data in that column. You must list them in the order you used in the previous line.
If you need to create a crosstab of a query that returns more than three columns, you can combine the columns in to one by using an array.