Thread: Rows as Columns

Hi everyone, I just joined this list a few days ago...

Now, I have a question, and I'm not even sure if this is possible
without having to make numerous statements.

Say I have two tables with the following columns and this data:

types:
id   | name
-----------------
1    | first_name
2    | last_name
3    | phone_number

data:
id   | data     | t_key | cust_id
---------------------------------
1    | Sam      | 1     | 1
2    | Smith    | 2     | 1
3    | 555-1212 | 3     | 1
4    | John     | 1     | 2


Types basically describes what the column type is.  t_key references the
type, cust_id is the user id.

i'd want to do something like "select t.name, d.data from types t, data
d where d.cust_id = 1"

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.

Any suggestions on this one?

This is obtuse, but it would work:  Have a function that would
dynamically build a view that has each of your type names as a column
name.  It could be triggered by any changes to the types table.  The
data table would be a left joined onto the customer table for each
column.  Then select from the view as you would any other view.

If you'd like some suggestions on the procedure, let me know.

--- James Taylor <jtx@hatesville.com> wrote:
> Hi everyone, I just joined this list a few days ago...
> 
> Now, I have a question, and I'm not even sure if this is possible
> without having to make numerous statements.
> 
> Say I have two tables with the following columns and this data:
> 
> types:
> id   | name
> -----------------
> 1    | first_name
> 2    | last_name
> 3    | phone_number
> 
> data:
> id   | data     | t_key | cust_id
> ---------------------------------
> 1    | Sam      | 1     | 1
> 2    | Smith    | 2     | 1
> 3    | 555-1212 | 3     | 1
> 4    | John     | 1     | 2
> 
> 
> Types basically describes what the column type is.  t_key references
> the
> type, cust_id is the user id.
> 
> i'd want to do something like "select t.name, d.data from types t,
> data
> d where d.cust_id = 1"
> 
> 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.
> 
> Any suggestions on this one?


__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com

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