Re: Transform table data

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