Hi all,
The recent discussion about generate_series() made me realise you can use it to transpose rows; meaning you can turn
columnsof 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!
