From: Hans-Jürgen Schönig <hs@cybertec.at>
To: pgsql-sql@postgresql.org
Subject: Re: Transposing data
Date: Mon, 21 May 2001 09:23:06 +0200
Message-ID: <3B08C25A.17F695BD@cybertec.at> (raw)
References: <3B081E2D.BB24DBE3@cybertec.at>
<9ead2o$1rd7$1@news.tht.net>
I have also thought of a solution like that but the problem is that I do not
have a complete list of all values occuring in the column so it has to be
dynamical. The second problem is that the amount of data involved may become
huge.
Hans
Alexander Dederer schrieb:
> 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.
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: hs@cybertec.at
Subject: Re: Transposing data
In-Reply-To: <3B08C25A.17F695BD@cybertec.at>
* 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