Skip site navigation (1) Skip section navigation (2)

Re: Transform table data

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Transform table data
Date: 2009-05-15 09:31:49
Message-ID: 20090515093149.GD20934@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Ognjen BlagojevicDate: 2009-05-15 10:16:19
Subject: Re: 'Hot' backup of PostgreSQL dbases
Previous:From: Jason Tan Boon TeckDate: 2009-05-15 08:43:48
Subject: Transform table data

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group