6 ms·
Whenever I needed to do a pivot in postgres, I used this approach that is described in this stackoverflow anwser: https://stackoverflow.com/questions/20618323/
by kanobi 2y ago
Whenever I needed to do a pivot in postgres, I used this approach that is described in this stackoverflow anwser:
https://stackoverflow.com/questions/20618323/create-a-pivot-table-with-postgresql#answer-54858085 https://stackoverflow.com/questions/20618323/create-a-pivot-...
So for example when you have a table like described (column_name, meta_key, value), you would create a query like this:
SELECT
column_name,
MAX(CASE WHEN meta_key='total_rows' THEN value ELSE NULL END) AS total_rows,
MAX(CASE WHEN meta_key='not_null_count' THEN value ELSE NULL END) AS not_null_count,
-- for all other metrics....
FROM tall_table
GROUP BY 1
(edit: formatting)
- jasonpbecker 2y agoMost of the time I'm using `filter ... where` for cases like these... for example select column_name, MAX(value) FILTER (where meta_key='total_rows') as total_row, MAX(value) FILTER (where meta_key='not_null_count') as not_null_count, ROUND(SUM (amount_in_cents) FILTER (WHERE EXTRACT(MONTH FROM TIMESTAMP '2006-01-01 03:04:05) = 1) / 100.0, 2) as 'january_sub_total' FROM table GROUP BY column_name
- kanobi 2y agoYup, that's even better. I think it didn't work for me because I was using Redshift, which didn't support FILTER at that time.