Обсуждение: Transform table data
I have a table A of data: [id][parameter][value] ----------------------------- [01][aa][20] [02][bb][21] [03][cc][22] [04][aa][23] [05][bb][24] [06][cc][25] [07][aa][26] [08][bb][27] [09][cc][28] that i would like to convert into the following table B: [key][aa][bb][cc] --------------------- [001][20][21][22] [002][23][24][25] [003][26][27][28] I program in PHP5, and would use arrays to transform it. But, I believe that if table A is large, I am going to run out of working memory or having a server timed out, before I can finish the task. Can I make postgresql perform this task instead? I am not sure where to find any reference to do this in postgresql. Thanks in advance. -- Jason Tan Boon Teck
In response to Jason Tan Boon Teck : > I have a table A of data: > > [id][parameter][value] > ----------------------------- > [01][aa][20] > [02][bb][21] > [03][cc][22] > [04][aa][23] > [05][bb][24] > [06][cc][25] > [07][aa][26] > [08][bb][27] > [09][cc][28] > > that i would like to convert into the following table B: > > [key][aa][bb][cc] > --------------------- > [001][20][21][22] > [002][23][24][25] > [003][26][27][28] For your example: test=*# select * from jason ; id | parameter | value ----+-----------+------- 1 | aa | 20 2 | bb | 21 3 | cc | 22 4 | aa | 23 5 | bb | 24 6 | cc | 25 7 | aa | 26 8 | bb | 27 9 | cc | 28 (9 rows) test=*# select case when id%3 = 0 then id/3 else (id/3)+1 end as key, sum(case when parameter='aa' then value else 0 end) as aa, sum(case when parameter='bb' then value else 0 end) as bb, sum(case when parameter='cc' then value else 0 end) as cc from jason group by 1 order by 1; key | aa | bb | cc -----+----+----+---- 1 | 20 | 21 | 22 2 | 23 | 24 | 25 3 | 26 | 27 | 28 (3 rows) But it works only if you have propper data, in particular if have 3 parameters and the id contains no gaps. Hope that helps, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Hi Andreas, Thank you for the prompt reply. I'm a real novice here, so here's some questions: 1. The values for 'cc' are not supposed to be in any order (could even be strings), then said statement would not work, right? 2. What if i would like to use timestamp as the primary key. Therefore, the key would not be in sequential order. Is this possible? 3. Does this select statement just display the output as shown? Is there any method that I can use to transfer this output into another table, performed by postgresql instead of by programming in another language? My concern is time outs if the dataset is too large, bearing in mind that I'm developing a PHP web based app. 4. My output from your SQL statement gives a different answer. Am i doing it wrong? kguan=# select * from jason; id | parameter | value ----+-----------+------- 1 | aa | 20 2 | bb | 21 3 | cc | 22 4 | aa | 23 5 | bb | 24 6 | cc | 25 7 | aa | 26 8 | bb | 27 9 | cc | 28 (9 rows) kguan=# select case kguan-# when id = 0 then id/3 else (id/3)+1 end as key, kguan-# sum(case when parameter='aa' then value else 0 end) as aa, kguan-# sum(case when parameter='bb' then value else 0 end) as bb, kguan-# sum(case when parameter='cc' then value else 0 end) as cc kguan-# from jason group by 1 kguan-# order by 1; key | aa | bb | cc -----+----+----+---- 1 | 20 | 21 | 0 2 | 23 | 24 | 22 3 | 26 | 27 | 25 4 | 0 | 0 | 28 (4 rows) Thank you. Regards, Jason On Fri, May 15, 2009 at 5:31 PM, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote: > In response to Jason Tan Boon Teck : >> I have a table A of data: >> >> [id][parameter][value] >> ----------------------------- >> [01][aa][20] >> [02][bb][21] >> [03][cc][22] >> [04][aa][23] >> [05][bb][24] >> [06][cc][25] >> [07][aa][26] >> [08][bb][27] >> [09][cc][28] >> >> that i would like to convert into the following table B: >> >> [key][aa][bb][cc] >> --------------------- >> [001][20][21][22] >> [002][23][24][25] >> [003][26][27][28] > > For your example: > > test=*# select * from jason ; > id | parameter | value > ----+-----------+------- > 1 | aa | 20 > 2 | bb | 21 > 3 | cc | 22 > 4 | aa | 23 > 5 | bb | 24 > 6 | cc | 25 > 7 | aa | 26 > 8 | bb | 27 > 9 | cc | 28 > (9 rows) > > test=*# select case when id%3 = 0 then id/3 else (id/3)+1 end as key, > sum(case when parameter='aa' then value else 0 end) as aa, sum(case when > parameter='bb' then value else 0 end) as bb, sum(case when > parameter='cc' then value else 0 end) as cc from jason group by 1 order > by 1; > key | aa | bb | cc > -----+----+----+---- > 1 | 20 | 21 | 22 > 2 | 23 | 24 | 25 > 3 | 26 | 27 | 28 > (3 rows) > > > But it works only if you have propper data, in particular if have 3 > parameters and the id contains no gaps. > > > Hope that helps, Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > -- Jason Tan Boon Teck
In response to Jason Tan Boon Teck :
> Hi Andreas,
>
>
> Thank you for the prompt reply. I'm a real novice here, so here's some
> questions:
>
> 1. The values for 'cc' are not supposed to be in any order (could even
> be strings), then said statement would not work, right?
It works only with numerical data, not with strings, because of the
sum()-function.
>
> 2. What if i would like to use timestamp as the primary key.
> Therefore, the key would not be in sequential order. Is this possible?
I think no. Not in plain SQL. Maybe with a function, plpgsql.
>
> 3. Does this select statement just display the output as shown? Is
> there any method that I can use to transfer this output into another
> table, performed by postgresql instead of by programming in another
> language? My concern is time outs if the dataset is too large, bearing
> in mind that I'm developing a PHP web based app.
Sure, you can use CREATE TABLE new_table AS SELECT ...
>
> 4. My output from your SQL statement gives a different answer. Am i
> doing it wrong?
Yes:
>
> kguan=# select case
> kguan-# when id = 0 then id/3 else (id/3)+1 end as key,
when id%3 = 0 then id/3 else (id/3)+1 end as key,
Do you see the difference?
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net