Sim Sim - 1 year ago 399
SQL Question

Pivot a table with Amazon RedShift / PostgreSQL

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

Answer Source

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
      ,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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download