pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Transposing data
3+ messages / 2 participants
[nested] [flat]

* Transposing data
@ 2001-05-20 19:42  Hans-Jürgen Schönig <hs@cybertec.at>
  0 siblings, 1 reply; 3+ messages in thread

From: Hans-Jürgen Schönig @ 2001-05-20 19:42 UTC (permalink / raw)
  To: pgsql-sql

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?

    Hans




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Transposing data
@ 2001-05-21 06:39  Alexander Dederer <dederer@spb.cityline.ru>
  parent: Hans-Jürgen Schönig <hs@cybertec.at>
  0 siblings, 1 reply; 3+ messages in thread

From: Alexander Dederer @ 2001-05-21 06:39 UTC (permalink / raw)
  To: pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Transposing data
@ 2001-05-21 07:23  Hans-Jürgen Schönig <hs@cybertec.at>
  parent: Alexander Dederer <dederer@spb.cityline.ru>
  0 siblings, 0 replies; 3+ messages in thread

From: Hans-Jürgen Schönig @ 2001-05-21 07:23 UTC (permalink / raw)
  To: 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.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-05-21 07:23 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-20 19:42 Transposing data Hans-Jürgen Schönig <hs@cybertec.at>
2001-05-21 06:39 ` Alexander Dederer <dederer@spb.cityline.ru>
2001-05-21 07:23   ` Hans-Jürgen Schönig <hs@cybertec.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox