From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Cc: lud_nowhere_man@yahoo.com
Subject: Re: Generating a cross tab (pivot table)
Date: Mon, 11 Nov 2002 16:42:37 +0100
Message-ID: <3DCFCFED.1F81C503@rodos.fzk.de> (raw)
> 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
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, lud_nowhere_man@yahoo.com
Subject: Re: Generating a cross tab (pivot table)
In-Reply-To: <3DCFCFED.1F81C503@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