Re: Transposing data

From: Hans-Jürgen Schönig <hs(at)cybertec(dot)at>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Transposing data
Date: 2001-05-21 07:23:06
Message-ID: 3B08C25A.17F695BD@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Bruno Boettcher 2001-05-21 08:33:33 how to check presence of a function and set permissions?
Previous Message Alexander Dederer 2001-05-21 06:39:19 Re: Transposing data