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   |     1age_1    | 50k   |     2age_1    | more  |     2age_2    | 40k   |
2age_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 |    2age_2    |     |   2 |     |age_3    |     |   1 |
  |
 
(3 rows)


Alexander Dederer.