From: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
---|---|
To: | PGSQL Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Tip: Transposing rows using generate_series() |
Date: | 2011-02-03 20:09:48 |
Message-ID: | 249886B9-227F-46BA-BD94-150348FD6858@solfertje.student.utwente.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | pasman pasmański | 2011-02-03 21:35:10 | Re: isn't "insert into where not exists" atomic? |
Previous Message | Bob Price | 2011-02-03 20:02:53 | Re: how to avoid repeating expensive computation in select |