AWS Redshift Pivot Table All Sizes

Question

I follow the method to rotate a large table in redshift:

Expand a table using Amazon RedShift / PostgreSQL

However, I have a large number of groups to rotate, i.e. m1 , m2 , ... How can I iterate over all the different values ​​and apply the same logic to each of them and the alias of the resulting column names?

Answer 1

If you want to be able to rotate arbitrary numbers of groups, you can combine the groups into a JSON string, and then extract the groups that interest you using the JSON Function Redshift . You probably don't want to do this for very large datasets.

Here is a basic idea based on sample data in the question above :

 select DimensionA, DimensionB, json_extract_path_text(json_pivot, 'm1') m1, json_extract_path_text(json_pivot, 'm2') m2 from ( select DimensionA, DimensionB, '{' || listagg(quote_ident(MetricName) || ':' || quote_ident(MetricValue), ',') within group (order by MetricName) || '}' as json_pivot from to_pivot group by DimensionA, DimensionB ) 

In practice, you will not want to run it like this. An internal selection is what you would use to create your pivot table, and an external selection shows how to reference specific group values.

This does not account for duplicate group entries for the same dim combination, as shown below:

 DimensionA DimensionB MetricName MetricValue ---------- ---------- ---------- ----------- dimA1 dimB2 m1 v13 dimA1 dimB2 m1 v23 

If this is an opportunity in the data, then you will have to figure out how to deal with it. I am not sure how it will behave as implemented. I assume that the first occurrence will be retrieved.

This can be done using a combination of LISTAGG and REGEXP_SUBSTR , using two custom delimiters.

Using varchar(max) for the JSON column type will produce 65535 bytes, which should be a capacity of several thousand categories.

Explained somewhat differently here .