How do you create cross-tab (pivot) queries in SQL

3 months ago by
How can you turn a query in SQL into a cross-tab or mimic the behavior of a pivot table in Excel?
Community: Zimmer Biomet

1 Answer

3 months ago by

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
    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.

How did you get the code to format like that?
written 3 months ago by Christopher Hamel  

It was kind of a pain, but if you click on the icon that looks a semicolon between two curly braces, you can choose what kind of code you are inserting.  I chose SQL.

written 3 months ago by Brandon Hibbard  
Please login to add an answer/comment or follow this question.

Similar posts:
Search »