Rotate a table using Amazon RedShift

Question

I have several tables in Amazon RedShift that follow a pattern of multiple dimension columns and a couple of column name / value metrics.

DimensionA DimensionB MetricName MetricValue ---------- ---------- ---------- ----------- dimA1 dimB1 m1 v11 dimA1 dimB2 m1 v12 dimA1 dimB2 m2 v21 dimA2 dimB2 m1 v13 dimA3 dimB1 m2 v22 

I am looking for a good way to unwind / rotate data in a single line for each unique set of sizes, for example:

 DimensionA DimensionB m1 m2 ---------- ---------- --- --- dimA1 dimB1 v11 dimA1 dimB2 v12 v21 dimA2 dimB2 v13 dimA3 dimB1 v22 

What is a good template for generating queries that will perform this unwinding?

Amazon RedShift is based on ParAccel and supports PostgreSQL 8.0.2, which does not have crosstab , unnest , pivot or unpivot .

Answer 1

You can simply create a CASE statement for MetricName, but you will need to use an aggregate to make GROUP BY work.

 SELECT dimension_a ,dimension_b ,MAX(CASE WHEN metric_name = 'm1' THEN metric_value ELSE NULL END) m1 ,MAX(CASE WHEN metric_name = 'm2' THEN metric_value ELSE NULL END) m2 FROM my_table GROUP BY dimension_a ,dimension_b ; 

It is worth noting that Redshift object names are never case sensitive, but always contain the contents of the column, which is the default opposite for SQL Server.