Tip: Transposing rows using generate_series()

[Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>]

Hi all,

The recent discussion about generate_series() made me realise you can use it to transpose rows; meaning you can turn columns of each row into separate rows. Here's an example:

CREATE TABLE foobarbaz(
foo text,
bar text,
baz int
);

INSERT INTO foobarbaz (foo, bar, baz) VALUES ('Foo', 'Bar', 72), (
'fOo', 'bAr', 73);

SELECT s.i AS idx, CASE
WHEN s.i = 1 THEN s.i::text
WHEN s.i = 2 THEN foo
WHEN s.i = 3 THEN bar
WHEN s.i = 4 THEN baz::text
ELSE NULL END AS example
FROM foobarbaz, generate_series(1, 4, 1) AS s(i);

 idx | example 
-----+---------
   1 | 1
   1 | 1
   2 | Foo
   2 | fOo
   3 | Bar
   3 | bAr
   4 | 72
   4 | 73
(8 rows)

SELECT s.i AS idx, CASE
WHEN s.i = 1 THEN s.i::text
WHEN s.i = 2 THEN foo
WHEN s.i = 3 THEN bar
WHEN s.i = 4 THEN baz::text
ELSE NULL END AS example
FROM foobarbaz, generate_series(1, 4, 1) AS s(i) ORDER BY baz, s.i;

 idx | example 
-----+---------
   1 | 1
   2 | Foo
   3 | Bar
   4 | 72
   1 | 1
   2 | fOo
   3 | bAr
   4 | 73
(8 rows)

An extra column with the column-name is easily added using another CASE.

Cheers,

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4d4b0b9111738384014833!



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general