Thread: Re: Transposing data

Re: Transposing data

From
Hans-Jürgen Schönig
Date:
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.