Message-ID: <3E8A8282.2020603@joeconway.com>
Date: Tue, 01 Apr 2003 22:26:10 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.2.1) Gecko/20021130
MIME-Version: 1.0
To: James Taylor <jtx@hatesville.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Rows as Columns
References: <002801c2f8ad$4888c9f0$4500a8c0@theplague>
In-Reply-To: <002801c2f8ad$4888c9f0$4500a8c0@theplague>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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