Pivot a table with Amazon RedShift

Sim picture Sim · Dec 18, 2013 · Viewed 12.3k times · Source

I have several tables in Amazon RedShift that follow the pattern of several dimension columns and a pair of metric name/value columns.

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/pivot the data into a form of one row per each unique dimension set, e.g.:

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

What is a good pattern for generating queries that would 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

Joe Harris picture Joe Harris · Dec 18, 2013

You can just create a CASE statement per MetricName but you'll have to use an aggregate as well to make the 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
;

Worth noting that Redshift object names are never case sensitive but column content always is, which is the opposite of SQL Server defaults.