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.