Snowflake Stored Procedure Dynamic Column Pivot

Question

I am trying to create a stored procedure in Snowflake that pivot's the data in a derived table. The pivot columns are dynamic in nature. I have found a way to do this through parameter passing. I am trying to do the same without passing any parameters but the code is not working.

My approach has been to apply a loop at the table from which I am extracting the columns into a variable. Then pass this variable in the pivot. The while loop by itself seems to be working fine. The error comes when this variable is being passed to the pivot.

The code I am using:

CREATE OR REPLACE PROCEDURE LOOP_EXMPL_5()
returns  varchar
language javascript
as
$$ 
var column1 = 'qwerty';
var command = `SELECT ATTR_NAME FROM TBL_DIM`;
var stmt = snowflake.createStatement({sqlText: command});
var rs = stmt.execute();
while (rs.next()) 
   {
     var column1 = column1.concat(",","'",rs.getColumnValue(1),"'"); 
   }
var column2 = column1
var command_1 = `CREATE OR REPLACE VIEW HIERARCHY_VIEW_2 AS SELECT * FROM (SELECT MSTR.PROD_CODE AS 
PROD_CODE,DIM.ATTR_NAME AS ATTR_NAME,MSTR.ATTR_VALUE AS ATTR_VALUE FROM TBL_DIM DIM INNER JOIN 
TBL_MSTR MSTR ON DIM.ATTR_KEY=MSTR.ATTR_KEY ) Q
              PIVOT (MAX (Q.ATTR_VALUE) FOR Q.ATTR_NAME IN ('${column2}'))
              AS P
              ORDER BY P.PROD_CODE;`;
 var stmt_1 = snowflake.createStatement({sqlText: command_1});
 var rs_1 = stmt_1.execute();
 return 'success'
 $$;   

The error I am getting:

Execution error in store procedure LOOP_EXMPL_5: SQL compilation error: syntax error line 2 at position 73 unexpected 'Region'. At Statement.execute, line 16 position 21.

The variable value that is being passed:

qwerty,'Region','Sub-Region','Division','Company-Product','Company-Mfg','Company-Ship From to Customer','Business Unit','Category','Sub-Category','Segment','Sub-Segment','Brand','Aggregate Brand','Sub-Brand','PPG'

I will remove the qwerty part in the SQL somehow.

Answer 1

For your objectives, see:

• https://medium.com/snowflake/dynamic-pivots-in-sql-with-snowflake-c763933987c
• How to pivot on dynamic values in Snowflake

To debug the current problem: column1.concat(",","'",rs.getColumnValue(1),"'"); is not escaping and quoting the column names correctly.

Set the UDF to return that value and then the value of var command_1 so you can debug appropriately.

A good way to escape the columns:

select '\\'' 
        || listagg(distinct pivot_column, '\\',\\'') within group (order by pivot_column)
        || '\\'' 

And then use:

for pivot_column in (${col_list}))

Answer 2

Here is the working code. Thanks a lot for your help Felipe and Greg.

CREATE OR REPLACE PROCEDURE LOOP_EXMPL_9()
returns  varchar
language javascript
as
$$ 
  var column1 = "";
  var command = `SELECT ATTR_NAME FROM TBL_DIM`;
  var stmt = snowflake.createStatement({sqlText: command});
  var rs = stmt.execute();
  while (rs.next()) 
   {
     if (column1 != "") column1 += ",";
     column1 += `'${rs.getColumnValue (1)}'`;
   }
  var column2 = column1;
  var command_1 = `CREATE OR REPLACE VIEW HIERARCHY_VIEW_2 AS SELECT * FROM (SELECT 
  MSTR.PROD_CODE AS PROD_CODE,DIM.ATTR_NAME AS ATTR_NAME,MSTR.ATTR_VALUE AS ATTR_VALUE 
  FROM TBL_DIM DIM INNER JOIN TBL_MSTR MSTR ON DIM.ATTR_KEY=MSTR.ATTR_KEY ) Q
              PIVOT (MAX (Q.ATTR_VALUE) FOR Q.ATTR_NAME IN (${column2}))
              AS P
              ORDER BY P.PROD_CODE;`;
  var stmt_1 = snowflake.createStatement({sqlText: command_1});
  var rs_1 = stmt_1.execute();
  return 'success'   
$$;   

Answer 3

You can start with an empty string:

var column1 = "";

You can then concatenate the column list like this:

if (column1 != "") column1 += ",";
column1 += `"${rs.getColumnValue(1)}"`);

The reason you're getting the SQL syntax error is that the column names are in single quotes when they should be in double quotes.