Generating a cross tab (pivot table) - Mailing list pgsql-sql

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 |
19honey  |        19 |         0 |         2 |           21milk    |        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 |       19honey
     |        19 |     0 |  2 |       21milk         |        12 |     8 |        34 |       54sum 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