pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alexander Dederer <dederer@spb.cityline.ru>
To: pgsql-sql@postgresql.org
Subject: Re: Transposing data
Date: Mon, 21 May 2001 10:39:19 +0400
Message-ID: <9ead2o$1rd7$1@news.tht.net> (raw)
References: <3B081E2D.BB24DBE3@cybertec.at>

Hans-J?rgen Sch?nig wrote:

> I want the values in column label to be displayed in the a-axis. Is
> there an easy way to transform the data:
> Here is the input data:
>  age_code | label | count
> ----------+-------+-------
>  age_1    | 30k   |     1
>  age_1    | 50k   |     2
>  age_1    | more  |     2
>  age_2    | 40k   |     2
>  age_3    | 40k   |     1
> 
> I want the result to be:
> 
> age_code | 30k | 40k | 50k  | more
> -----------------------
> age_1       | 1      |         |    2   | 1
> age_2       |         |      2 |
> age_3       |          |     1 |         |
> 
> Is there any easy way to do the job or do I have to write a PL/pgSQL
> function?

Got it:
# SELECT * FROM aaa;
 age_code | label | count
----------+-------+-------
 age_1    | 30k   |     1
 age_1    | 50k   |     2
 age_1    | more  |     2
 age_2    | 40k   |     2
 age_3    | 40k   |     1

-------
SELECT 
  s0.age_code, 
  (SELECT count FROM aaa  s1 WHERE s1.age_code = s0.age_code AND s1.label = 
'30k') as "30k",
  (SELECT count FROM aaa  s1 WHERE s1.age_code = s0.age_code AND s1.label = 
'40k') as "40k",
  (SELECT count FROM aaa  s1 WHERE s1.age_code = s0.age_code AND s1.label = 
'50k') as "50k",
  (SELECT count FROM aaa  s1 WHERE s1.age_code = s0.age_code AND s1.label = 
'more')  as "more"
FROM aaa s0 
GROUP BY s0.age_code;

 age_code | 30k | 40k | 50k | more
----------+-----+-----+-----+------
 age_1    |   1 |     |   2 |    2
 age_2    |     |   2 |     |
 age_3    |     |   1 |     |
(3 rows)


Alexander Dederer.



view thread (3+ messages)  latest in thread

Message-ID: <9ead2o$1rd7$1@news.tht.net>
Permalink:  ../9ead2o$1rd7$1@news.tht.net/
Also on:    postgresql.org/message-id/9ead2o$1rd7$1@news.tht.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: dederer@spb.cityline.ru
  Subject: Re: Transposing data
  In-Reply-To: <9ead2o$1rd7$1@news.tht.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox