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
.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With