From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Cc: terry@ashtonwoodshomes.com
Cc: chaudhar@umich.edu
Subject: Generating a cross tab (pivot table)
Date: Thu, 07 Nov 2002 12:47:46 +0100
Message-ID: <3DCA52E2.5529E893@rodos.fzk.de> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, terry@ashtonwoodshomes.com, chaudhar@umich.edu
Subject: Re: Generating a cross tab (pivot table)
In-Reply-To: <3DCA52E2.5529E893@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox