Use Snowflake’s Pivot Function with a Dynamic List of Columns

Code used in the video is pasted below.

SELECT *
FROM "EXAMPLE_DB"."EXAMPLE_SCHEMA"."EXAMPLE_TABLE"
PIVOT(sum("VALUE") for
CATEGORY in ('AB', 'BA', 'AC', 'CA'))
;

SET column_list = (
SELECT LISTAGG(DISTINCT "CATEGORY", '\',\'')
FROM "EXAMPLE_DB"."EXAMPLE_SCHEMA"."EXAMPLE_TABLE"
WHERE CONTAINS("CATEGORY", 'A')
);

SELECT $column_list;

SET column_list2 = concat('(\'', $column_list, '\'))');

SELECT $column_list2;

set sql_query = concat('SELECT * FROM "EXAMPLE_DB"."EXAMPLE_SCHEMA"."EXAMPLE_TABLE" PIVOT(sum("VALUE") for CATEGORY in', $column_list2);

SELECT $sql_query;

EXECUTE IMMEDIATE $sql_query

[cargouvu]

How do you remove the single quotes on the column headers after the pivot? Also, I am unable to retrieve in another select statement once it has the single quotes.

[vardhanreddy]

Hi, Thanks for your info.Hi, I want to display column headings without single quotes before and after in the output. You displayed 'BA', 'AB', 'CA', and I aim to dynamically achieve BA AB CA.can you explain how to achieve BA AB CA

[pprathamesh]

Thanks, very helpful! Please make more videos on Snowflake.

I've been attempting a FIFO query in SF, where if I bought 5 qty at x price, sold 2 the next day, then again bought 5, and sold 4, how much was the profit by FIFO methodology, and how much qty is remaining at what price. It would be great if you could do a video on that!

[satyammarkam]

Hi Im getting error
As we can't store more than 225 char in variable
Do you have any other approach for this scenario?

[siddheshbandekar]

Hi,
Thanks this was very helpful. However I wanted to create a view of this data would it be possible ? Also is it possible to remove the quotes from the column names.

[assafhaim]

Doesn't work
I get error
Assignment to 'COLUMN_LIST' not done because value exceeds size limit for variables. Its size is 535; the limit is 256 (internal storage size in bytes).

[SalmanKhan]

how to get over the error where the list is too long to be truncated.

[jkim]

this looks like it be used to dynamically load some json data

[creedz-gvt]

Hi Is it possible to remove the single quote from column name?