Re: Transform table data

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