Tip: Transposing rows using generate_series()

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!