Re: Rows as Columns

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