Skip site navigation (1) Skip section navigation (2)

Re: Generating a cross tab (pivot table)

From: Christoph Haller <ch(at)rodos(dot)fzk(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Cc: lud_nowhere_man(at)yahoo(dot)com
Subject: Re: Generating a cross tab (pivot table)
Date: 2002-11-11 15:42:37
Message-ID: 3DCFCFED.1F81C503@rodos.fzk.de (view raw or flat)
Thread:
Lists: pgsql-sql
> 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


Responses

pgsql-sql by date

Next:From: Christoph HallerDate: 2002-11-11 15:55:45
Subject: Generating a cross tab II (pivot table)
Previous:From: Carlos SousaDate: 2002-11-11 14:57:34
Subject: bigger problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group