Message-ID: <3DCFCFED.1F81C503@rodos.fzk.de>
Date: Mon, 11 Nov 2002 16:42:37 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: lud_nowhere_man@yahoo.com
Subject: Re: Generating a cross tab (pivot table)
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit


> I saw something that might somewhat a bit more
> flexible solution using SQL. I don't know if it works
> in PostgreSQL. I saw it at the MySQL site.
>
>   The following is the URL:
>   http://www.mysql.com/articles/wizard/index.html
>
>   Has anyone tried this on a PostgreSQL database ?

No, not me.
But as far as I can tell the SQL statements can quite easily
be re-written in PostgreSQL:
e. g.
mysql> SELECT location, SUM(IF(gender='M',1,0)) AS M,
SUM(IF(gender='F',1,0)) AS F
     -> FROM locations INNER JOIN employees USING (loc_code) GROUP BY
location;
becomes
SELECT location,
SUM(CASE WHEN gender='M' THEN 1 ELSE 0 END) AS "M",
SUM(CASE WHEN gender='F' THEN 1 ELSE 0 END) AS "F",
FROM locations LEFT JOIN employees ON
(locations.loc_code=employees.loc_code)
GROUP BY location;

And this goes for the perl script as well.

Regards, Christoph