pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRows as Columns
3+ messages / 3 participants
[nested] [flat]
* Rows as Columns
@ 2003-04-02 00:17 James Taylor <jtx@hatesville.com>
2003-04-02 02:42 ` Re: Rows as Columns chester c young <chestercyoung@yahoo.com>
2003-04-02 06:26 ` Re: Rows as Columns Joe Conway <mail@joeconway.com>
0 siblings, 2 replies; 3+ messages in thread
From: James Taylor @ 2003-04-02 00:17 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Rows as Columns
2003-04-02 00:17 Rows as Columns James Taylor <jtx@hatesville.com>
@ 2003-04-02 02:42 ` chester c young <chestercyoung@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: chester c young @ 2003-04-02 02:42 UTC (permalink / raw)
To: James Taylor <jtx@hatesville.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Rows as Columns
2003-04-02 00:17 Rows as Columns James Taylor <jtx@hatesville.com>
@ 2003-04-02 06:26 ` Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 3+ messages in thread
From: Joe Conway @ 2003-04-02 06:26 UTC (permalink / raw)
To: James Taylor <jtx@hatesville.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-04-02 06:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-02 00:17 Rows as Columns James Taylor <jtx@hatesville.com>
2003-04-02 02:42 ` chester c young <chestercyoung@yahoo.com>
2003-04-02 06:26 ` Joe Conway <mail@joeconway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox