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