AWS Redshift: columns into rows (wide to long)

Question

I'm writing a query which result will be displayed as dashboard on redash. The query is basically a sum of several columns of table within a specific time range:

SELECT
   sum(col_A),
   sum(col_B),
   sum(col_C)
FROM
   table_X
WHERE
   timestamp > '2018-01-01'
   AND timestamp < '2018-02-01'

The result is something like this:

col_A    col_B    col_C    
123      456      789

However, to render the dasboard on redash properly, I need the result in long format:

column   sum
col_A    123
col_B    456
col_C    789

How can I cast the result from wide to long format?

score 5 · Accepted Answer · answered Feb 23 '18 at 21:45

A simple way is to use union all:

SELECT sum(col_A) FROM table_X WHERE timestamp > '2018-01-01' AND timestamp < '2018-02-01'
UNION ALL
SELECT sum(col_B) FROM table_X WHERE timestamp > '2018-01-01' AND timestamp < '2018-02-01'
UNION ALL
SELECT sum(col_C) FROM table_X WHERE timestamp > '2018-01-01' AND timestamp < '2018-02-01';

You may need to be careful about the ordering. I would include a second column specifying which is being aggregated.

Three separate queries is not quite as bad as it sounds. Redshift is a columnar database, so only the columns references should be read.

AWS Redshift: columns into rows (wide to long)

1 Answers1