Redshift - Many Columns to Rows (Unpivot)

Question

In Redshift :

I've a table with 30 dimension fields and more than 150 measure fields.
To make good use of these data in a visualization tool (Tableau), I need to Unpivot the measure columns into only one measure and one dimension to categorize them.

Short Example:

   Date         Country    Order     Banana  Apple  Orange  Kiwi Lemon

    1-10-2018    Belgium    XYZ789    14       0     10      16    7
    1-10-2018    Germany    ABC123    10      15      3      15    3
    2-10-2018    Belgium    KLM456     9       9      7       1    7

Result :

   Date         Country    Order     Measure_Name   Measure_Value
    1-10-2018    Belgium    XYZ789    Banana         14
    1-10-2018    Belgium    XYZ789    Apple           0
    1-10-2018    Belgium    XYZ789    Orange         10
    1-10-2018    Belgium    XYZ789    Kiwi           16
    1-10-2018    Belgium    XYZ789    Lemon           7
    1-10-2018    Germany    ABC123    Banana         10
    1-10-2018    Germany    ABC123    Apple          15
    1-10-2018    Germany    ABC123    Orange          3
    1-10-2018    Germany    ABC123    Kiwi           15
    1-10-2018    Germany    ABC123    Lemon           3
    2-10-2018    Belgium    KLM456    Banana          9
    2-10-2018    Belgium    KLM456    Apple           9
    2-10-2018    Belgium    KLM456    Orange          7
    2-10-2018    Belgium    KLM456    Kiwi            1
    2-10-2018    Belgium    KLM456    Lemon           7

I know and I've tried the 'UNION ALL' solution but my table count millions of rows, and more than 150 columns to unpivot is really too huge for this solution. (Even The SQL is more than 8k rows long)

Do you have any Idea to help me ?

Thanks a lot,

Answer 1

When writing this code in an 'imperative' way, you'd like to generate more rows out of one, possibly using something like flatMap (or equivalent in your programming language). To generate rows in SQL, you have to use JOIN.

This problem can be solved by (CROSS)JOINing your table with another, having as many rows as there are columns to unpivot. You need to add some conditional magic and Voila!.

CREATE TABLE t (
  "Date" date, 
  "Country" varchar, 
  "Order" varchar, 
  "Banana" varchar, 
  "Apple" varchar, 
  "Orange" varchar, 
  "Kiwi" varchar, 
  "Lemon" varchar
);

INSERT INTO t VALUES ('1-10-2018', 'Belgium', 'XYZ789', '14', '0', '10', '16', '7');
INSERT INTO t VALUES ('1-10-2018', 'Germany', 'ABC123', '10', '15', '3', '15', '3');
INSERT INTO t VALUES ('2-10-2018', 'Belgium', 'KLM456', '9', '9', '7', '1', '7');

WITH 
    cols as (
      select 'Banana' as c
      union all 
      select 'Apple' as c
      union all 
      select 'Orange' as c
      union all 
      select 'Kiwi' as c
      union all 
      select 'Lemon' as c
      )
select 
    "Date", 
    "Country", 
    "Order",
    c "Fruit Type",
    CASE c 
        WHEN 'Banana' THEN "Banana" 
        WHEN 'Apple' THEN "Apple"
        WHEN 'Orange' THEN "Orange"
        WHEN 'Kiwi' THEN "Kiwi"
        WHEN 'Lemon' THEN "Lemon"
        ELSE NULL
    END as "Amount Ordered"

from t cross join cols;

https://www.db-fiddle.com/f/kojuPAjpS5twCKXSPVqYyP/3

Answer 2

Given that you have 150 columns to transpose, I do not think its feasible to do it with SQL. I have had almost the same exact scenario and used python to solve it. The pseudo-code and explanation is in this question

Redshift. How can we transpose (dynamically) a table from columns to rows?