Re: Transposing data

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.