From: Joe Conway <mail@joeconway.com>
To: James Taylor <jtx@hatesville.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Rows as Columns
Date: Tue, 01 Apr 2003 22:26:10 -0800
Message-ID: <3E8A8282.2020603@joeconway.com> (raw)
In-Reply-To: <002801c2f8ad$4888c9f0$4500a8c0@theplague>
References: <002801c2f8ad$4888c9f0$4500a8c0@theplague>
James Taylor wrote:
> This would return something like:
>
> name | data
> ------------------------
> first_name | Sam
> last_name | Smith
> phone_number | 555-1212
>
>
> Well, I need it to somehow return that data in this format:
>
> first_name | last_name | phone_number
> ----------------------------------------
> Sam | Smith | 555-1212
>
> The information in Types is not static, so I can't declare the col names
> based on what you see here.
>
If you know at query writing time, which attributes (distinct values of
name from the types table) you want, then you could use the crosstab
function from contrib/tablefunc (except you'll need a newer version --
see url below):
regression=# select d.cust_id, t.name, d.data from types t, data d where
d.t_key = t.id;
cust_id | name | data
---------+--------------+----------
1 | first_name | Sam
2 | first_name | John
1 | last_name | Smith
1 | phone_number | 555-1212
(4 rows)
regression=# select * from crosstab('select d.cust_id, t.name, d.data
from types t, data d where d.t_key = t.id order by 1','select distinct
name from types') as (cust_id int, fn text, ln text, pn text);
cust_id | fn | ln | pn
---------+------+-------+----------
1 | Sam | Smith | 555-1212
2 | John | |
(2 rows)
The version of crosstab() distributed with Postgres 7.3.x cannot do
exactly this, but you can get the latest (same as what is in cvs for
7.4) here:
http://www.joeconway.com/
You want "contrib/tablefunc with hashed crosstab"
HTH,
Joe
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: mail@joeconway.com, jtx@hatesville.com
Subject: Re: Rows as Columns
In-Reply-To: <3E8A8282.2020603@joeconway.com>
* 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