pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTransposing data
3+ messages / 2 participants
[nested] [flat]
* Transposing data
@ 2001-05-20 19:42 Hans-Jürgen Schönig <hs@cybertec.at>
2001-05-21 06:39 ` Re: Transposing data Alexander Dederer <dederer@spb.cityline.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Hans-Jürgen Schönig @ 2001-05-20 19:42 UTC (permalink / raw)
To: pgsql-sql
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?
Hans
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Transposing data
2001-05-20 19:42 Transposing data Hans-Jürgen Schönig <hs@cybertec.at>
@ 2001-05-21 06:39 ` Alexander Dederer <dederer@spb.cityline.ru>
2001-05-21 07:23 ` Re: Transposing data Hans-Jürgen Schönig <hs@cybertec.at>
0 siblings, 1 reply; 3+ messages in thread
From: Alexander Dederer @ 2001-05-21 06:39 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Transposing data
2001-05-20 19:42 Transposing data Hans-Jürgen Schönig <hs@cybertec.at>
2001-05-21 06:39 ` Re: Transposing data Alexander Dederer <dederer@spb.cityline.ru>
@ 2001-05-21 07:23 ` Hans-Jürgen Schönig <hs@cybertec.at>
0 siblings, 0 replies; 3+ messages in thread
From: Hans-Jürgen Schönig @ 2001-05-21 07:23 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-05-21 07:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-20 19:42 Transposing data Hans-Jürgen Schönig <hs@cybertec.at>
2001-05-21 06:39 ` Alexander Dederer <dederer@spb.cityline.ru>
2001-05-21 07:23 ` Hans-Jürgen Schönig <hs@cybertec.at>
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