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