CODEWITHSUNDEEP
sqlpivotamazon-redshiftparaccel
Sim
Sim

Reputation: 13548

Pivot a table with Amazon RedShift

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.

Upvotes: 5

Views: 13417

Answers (1)

Joe Harris
Joe Harris

Reputation: 14045

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.

Upvotes: 11

Related Questions