Skip to content

Instantly share code, notes, and snippets.

@zmiftah

zmiftah/sql_postgres_pivot_tablefunc.sql

Created June 10, 2015 08:22
Show Gist options
  • Save zmiftah/a8cbbd4d514e83d6fae7 to your computer and use it in GitHub Desktop.
Save zmiftah/a8cbbd4d514e83d6fae7 to your computer and use it in GitHub Desktop.
Download ZIP
Postgres Pivot tablefunc
Raw
sql_postgres_pivot_tablefunc.sql
-- Enable tablefunc
CREATE EXTENSION tablefunc;
-- Original Query Data
SELECT generate_series AS date,
b.desc AS TYPE,
(random() * 10000 + 1)::int AS val
FROM generate_series((now() - '100 days'::interval)::date, now()::date, '1 day'::interval),
(SELECT unnest(ARRAY['OSX', 'Windows', 'Linux']) AS DESC) b;
-- Pivot
SELECT *
FROM crosstab(
'SELECT
a date,
b.desc AS os,
(random() * 10000 + 1)::int AS value
FROM generate_series((now() - ''100 days''::interval)::date, now()::date, ''1 DAY''::interval) a,
(SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux'']) AS DESC) b ORDER BY 1,2
','SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux''])'
)
AS ct(date date, OSX int, Windows int, Linux int);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment