Proper way to create a pivot table with crosstab

Question

How do I convert the following query into a pivot table using crosstab?

select (SUM(CASE WHEN added_customer=false
        THEN 1
        ELSE 0 
        END)) AS CUSTOMERS_NOT_ADDED, (SUM(CASE WHEN added_customer=true
        THEN 1
        ELSE 0 
        END)) AS CUSTOMERS_ADDED, 

        (select (SUM(CASE WHEN added_sales_order=false
                THEN 1
                ELSE 0 
                END))  
        FROM shipments_data
        ) AS SALES_ORDER_NOT_ADDED, 

        (select (SUM(CASE WHEN added_sales_order=true
                THEN 1
                ELSE 0 
                END))  
        FROM shipments_data
        ) AS SALES_ORDER_ADDED,


        (select (SUM(CASE WHEN added_fulfillment=false
                THEN 1
                ELSE 0 
                END))  
        FROM shipments_data
        ) AS ITEM_FULFILLMENT_NOT_ADDED, 

        (select (SUM(CASE WHEN added_fulfillment=true
                THEN 1
                ELSE 0 
                END))  
        FROM shipments_data
        ) AS ITEM_FULFILLMENT_ADDED,


        (select (SUM(CASE WHEN added_invoice=false
                THEN 1
                ELSE 0 
                END))  
        FROM shipments_data
        ) AS INVOICE_NOT_ADDED, 

        (select (SUM(CASE WHEN added_invoice=true
                THEN 1
                ELSE 0 
                END))  
        FROM shipments_data
        ) AS INVOICE_ADDED,

        (select (SUM(CASE WHEN added_ra=false
                THEN 1
                ELSE 0 
                END))  
        FROM shipments_data
        ) AS RA_NOT_ADDED, 

        (select (SUM(CASE WHEN added_ra=true
                THEN 1
                ELSE 0 
                END))  
        FROM shipments_data
        ) AS RA_ADDED,

        (select (SUM(CASE WHEN added_credit_memo=false
                THEN 1
                ELSE 0 
                END))  
        FROM shipments_data
        ) AS CREDIT_MEMO_NOT_ADDED, 

        (select (SUM(CASE WHEN added_credit_memo=true
                THEN 1
                ELSE 0 
                END))  
        FROM shipments_data
        ) AS CREDIT_MEMO_ADDED

FROM shipments_data;

This query gives me data in a standard row format however I would like to show this as a pivot table in the following format:

                    Added    Not_Added
Customers             100            0
Sales Orders           50           50
Item Fulfillemnts       0          100
Invoices                0          100
...

I am using Heroku PostgreSQL, which is running v9.1.6

Also, I'm not sure if my above query can be optimized or if this is poor form. If it can be optimized/improved I would love to learn how.

The [tablefunc](http://www.postgresql.org/docs/current/interactive/tablefunc.html) module that supplies `crosstab()` is available for 9.1 (like for any other version this side of the millennium). Doesn't Heroku let you install additional modules? Have you tried [`CREATE EXTENSION tablefunc`](http://www.postgresql.org/docs/current/interactive/sql-createextension.html)? — Erwin Brandstetter, Jan 30 '13 at 18:32
I haven't tried that - I'll post back with results in a few mins. — Robert H, Jan 30 '13 at 18:55
You may be interested in the additional solution appended to my answer. — Erwin Brandstetter, Jan 31 '13 at 22:37

score 2 · Accepted Answer · edited May 23 '17 at 12:04

The tablefunc module that supplies crosstab() is available for 9.1 (like for any other version this side of the millennium). Doesn't Heroku let you install additional modules? Have you tried:

CREATE EXTENSION tablefunc;

For examples how to use it, refer to the manual or this related question:
PostgreSQL Crosstab Query

OR try this search - there are a couple of good answers with examples on SO.

To get you started (like most of the way ..) use this largely simplified and re-organized query as base for the crosstab() call:

SELECT 'added'::text AS col
      ,SUM(CASE WHEN added_customer    THEN 1 ELSE 0 END) AS customers
      ,SUM(CASE WHEN added_sales_order THEN 1 ELSE 0 END) AS sales_order
      ,SUM(CASE WHEN added_fulfillment THEN 1 ELSE 0 END) AS item_fulfillment
      ,SUM(CASE WHEN added_invoice     THEN 1 ELSE 0 END) AS invoice
      ,SUM(CASE WHEN added_ra          THEN 1 ELSE 0 END) AS ra
      ,SUM(CASE WHEN added_credit_memo THEN 1 ELSE 0 END) AS credit_memo
FROM   shipments_data

UNION ALL
SELECT 'not_added' AS col
      ,SUM(CASE WHEN NOT added_customer    THEN 1 ELSE 0 END) AS customers
      ,SUM(CASE WHEN NOT added_sales_order THEN 1 ELSE 0 END) AS sales_order
      ,SUM(CASE WHEN NOT added_fulfillment THEN 1 ELSE 0 END) AS item_fulfillment
      ,SUM(CASE WHEN NOT added_invoice     THEN 1 ELSE 0 END) AS invoice
      ,SUM(CASE WHEN NOT added_ra          THEN 1 ELSE 0 END) AS ra
      ,SUM(CASE WHEN NOT added_credit_memo THEN 1 ELSE 0 END) AS credit_memo
FROM   shipments_data;

If your columns are defined NOT NULL, you can further simplify the CASE expressions.

If performance is crucial, you can get all aggregates in a single scan in a CTE and split values into two rows in the next step.

WITH x AS (
   SELECT count(NULLIF(added_customer, FALSE)) AS customers
         ,sum(added_sales_order::int)          AS sales_order
          ...
         ,count(NULLIF(added_customer, TRUE))  AS not_customers
         ,sum((NOT added_sales_order)::int)    AS not_sales_order
          ...
   FROM   shipments_data
   )
SELECT 'added'::text AS col, customers, sales_order, ... FROM x
UNION  ALL
SELECT 'not_added', not_customers, not_sales_order, ...  FROM x;

I also demonstrate two alternative ways to build your aggregates - both built on the assumption that all columns are boolean NOT NULL. Both alternatives are syntactically shorter, but not faster. In previous testes all three methods performed about the same.

Proper way to create a pivot table with crosstab

1 Answers1