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
