Message-ID: <3DCA52E2.5529E893@rodos.fzk.de>
Date: Thu, 07 Nov 2002 12:47:46 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: terry@ashtonwoodshomes.com, chaudhar@umich.edu
Subject: Generating a cross tab (pivot table)
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit


Regarding to Terry's request on multiple aggregates and
Shahbaz's request for generating a cross tab ( pivot table )
in September, I've found an excellent example on a german inet page
http://www.itrain.de/
I've translated it and think it's useful for many who subscribed
(I hope so, maybe it was told before, but I couldn't find
anything about this topic in the techdecs).

Objective:
There is a relation "sales",
holding the sales of different products of different vendors.
The task is to generate a report which shows the sales
of every vendor and every product.

Consider the following table populated with some data:
CREATE TABLE sales (
 product TEXT,
 vendor  TEXT,
 sales   INTEGER
);

INSERT INTO sales VALUES ( 'milk'  , 'mr. pink'  , 12 ) ;
INSERT INTO sales VALUES ( 'milk'  , 'mr. brown' ,  8 ) ;
INSERT INTO sales VALUES ( 'honey' , 'mr. green' ,  2 ) ;
INSERT INTO sales VALUES ( 'milk'  , 'mr. green' , 34 ) ;
INSERT INTO sales VALUES ( 'butter', 'mr. pink'  , 17 ) ;
INSERT INTO sales VALUES ( 'butter', 'mr. brown' ,  2 ) ;
INSERT INTO sales VALUES ( 'honey' , 'mr. pink'  , 19 ) ;

The following query generates the report:
SELECT product,
       SUM(CASE vendor WHEN 'mr. pink'  THEN sales ELSE 0 END) AS "mr.
pink ",
       SUM(CASE vendor WHEN 'mr. brown' THEN sales ELSE 0 END) AS "mr.
brown",
       SUM(CASE vendor WHEN 'mr. green' THEN sales ELSE 0 END) AS "mr.
green",
       SUM(sales) AS "sum of sales"
FROM sales GROUP BY product ;

 product | mr. pink  | mr. brown | mr. green | sum of sales
---------+-----------+-----------+-----------+--------------
 butter  |        17 |         2 |         0 |           19
 honey   |        19 |         0 |         2 |           21
 milk    |        12 |         8 |        34 |           54
(3 rows)

The example is based on MS SQL Server 7.0 and it appears to be
there is a valuable feature called CUBE which completes the report.

SELECT CASE WHEN GROUPING(product) = 1 THEN 'sum of sales' ELSE product
END,
       SUM(CASE vendor WHEN 'mr. pink'  THEN sales ELSE 0 END) AS "mr.
pink ",
       SUM(CASE vendor WHEN 'mr. brown' THEN sales ELSE 0 END) AS "mr.
brown",
       SUM(CASE vendor WHEN 'mr. green' THEN sales ELSE 0 END) AS "mr.
green",
       SUM(sales) AS "sum of sales"
FROM sales GROUP BY product WITH CUBE ;

 product      | mr. pink  | mr. brown | mr. green | sum of sales
--------------+-----------+-----------+-----------+--------------
 butter       |        17 |     2 |  0 |       19
 honey        |        19 |     0 |  2 |       21
 milk         |        12 |     8 |        34 |       54
 sum of sales |        48 |        10 |        36 |           94
(4 rows)

I would like to hear from the core team whether they think this feature
is worthy to be implemented, or even better, is there a similar one or
an easy workaround already.

It's obvious this approach is most inflexible.
As soon as there is a new vendor, one has to re-write the query and add
SUM(CASE vendor WHEN 'mr. new' THEN ... ,

In an advanced example it is shown how to deal with cross tabs in
general
using a stored procedure. I am going to translate this and re-write it
for postgres, too (ok, I will try).

Regards, Christoph