Re: function to return rows as columns? - Mailing list pgsql-general

In response to Linos :
> Hello,
>     i have a query that returns a result set like this:
>
> item | size | stock
> 123  | XL   | 10
> 123  | XXL | 5
> 123  | XS   | 3
>
> and i would like get the results like this:
>
> item | XL | XXL | XS
> 123  | 10  | 5     | 3

Other solution with plain SQL:

test=*# select * from linos ;
 item | size | stock
------+------+-------
  123 | XL   |    10
  123 | XXL  |     5
  123 | XS   |     3
(3 rows)

test=*# select item, sum(case when size='XL' then stock else 0 end) as
"XL", sum(case when size='XXL' then stock else 0 end) as "XXL", sum(case
when size='XS' then stock else 0 end) as "XS" from linos where item=123
group by item;
 item | XL | XXL | XS
------+----+-----+----
  123 | 10 |   5 |  3
(1 row)


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net