Unpivot column name

2Teachis2LearnTwice 0
Viewed 47

I have a table currently that is at the row level, not the header level, and has a single line for each product entry on an order. I'd like to consolidate that into one line = several products. I could have an order with up to 20 products and 20 prices. Additionally, my product cardinality could be up to 80K SKU's.

note: SQL would be done in Snowflake

how can I achieve the desired table format?

Current:

Orders Product Price
Order 1 Product 1 Price 1
Order 1 Product 2 Price 2
Order 2 Product 1 Price 1
Order 2 Product 2 Price 2

Desired:

Orders Product 1 Price 1 Product 2 Price 2 Product 3 Price 3
Order 1 Product 1 Price 1 Product 2 Price 2 Product 3 Price 3

this is kind of what I had in mind for my SQL query.

select a.Order, a. Product, c. Price FROM productlisting a unpivot (????

pivot snowflake-cloud-data-platform sql
1 Answers

In Snowflake you are limited to the size of a partition is around 16MB (it's in compressed format), so the amount of columns that you will be able to produce with your PIVOT query depends on the data types specified for the long list of columns.

That said, there is no native way in Snowflake to dynamically produce the desired output, since with the Snowflake's PIVOT you need to specify all the column names. However, you can use Python (Snowflake's Python connector) to pull the distinct values and dynamically generate (and execute) your SQL statement that uses the PIVOT.

You can also test the solution by @felipe-hoffa. He created a Snowflake procedure to dynamically generate PIVOT queries:

https://github.com/fhoffa/snowflake_snippets/tree/main/pivots

I have tested it on your sample dataset and it worked:

  1. Create sample table
    CREATE TEMPORARY TABLE productlisting AS  
    (
        SELECT 'Order 1' AS Orders, 'Product 1' AS Product, 'Price 1' AS Price UNION ALL
        SELECT 'Order 1' AS Orders, 'Product 2' AS Product, 'Price 2' AS Price UNION ALL
        SELECT 'Order 1' AS Orders, 'Product 3' AS Product, 'Price 3' AS Price UNION ALL
        SELECT 'Order 2' AS Orders, 'Product 1' AS Product, 'Price 1' AS Price UNION ALL
        SELECT 'Order 2' AS Orders, 'Product 2' AS Product, 'Price 2' AS Price
    )
    ;
  1. Create a procedure in your Snowflake database
    create or replace procedure pivot_prev_results()
    returns string
    language javascript
    execute as caller as
    $$
      var cols_query = `
          select '\\'' 
            || listagg(distinct pivot_column, '\\',\\'') within group (order by pivot_column)
            || '\\'' 
          from table(result_scan(last_query_id(-1)))`;
      var stmt1 = snowflake.createStatement({sqlText: cols_query});
      var results1 = stmt1.execute();
      results1.next();
      var col_list = results1.getColumnValue(1);
      
      pivot_query = `
             select * 
             from (select * from table(result_scan(last_query_id(-2)))) 
             pivot(max(pivot_value) for pivot_column in (${col_list}))
         `
      var stmt2 = snowflake.createStatement({sqlText: pivot_query});
      stmt2.execute();
      return `select * from table(result_scan('${stmt2.getQueryId()}'));\n  select * from table(result_scan(last_query_id(-2)));`;
    $$;
  1. Pull the desired data and assign pivot columns and pivot values
    SELECT
        Orders,
        Product AS pivot_column,
        Price AS pivot_value
    FROM
        productlisting
    ;
  1. Call the procedure function
    call pivot_prev_results();
  1. Output the pivoted table
    SELECT 
        * 
    FROM 
        table(result_scan(last_query_id(-2)))
    ORDER BY
        1
    ;

Dynamically Pivoted Output

stantyan0
Related