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

[@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.

[@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!

[@dotpi5907]

Hi Prathamesh, thats a great idea for a video! Ill let you know when it's done

[@dotpi5907]

Hi @Prathamesh Pathak, hope this video helps kzbin.info/www/bejne/hl64c3iAntqCgaM I'm having some problems pasting the code in the description because of some of the symbols, ill post it in the next comment for now

[@dotpi5907]

--create the initial tables -------------------------------------------------------------- --create a fifo schema create schema example_db.fifo; drop table sell; drop table buy; --create a buy table create or replace table example_db.fifo.buy ("INDEX" integer, quantity float, price float); --create a sell table create or replace table example_db.fifo.sell ("INDEX" integer, quantity float); --add values to the initial tables -------------------------------------------------------------- insert into example_db.fifo.buy ("INDEX", quantity, price) values (1, 200, 10), (2, 150, 12), (3, 225, 16); insert into example_db.fifo.sell ("INDEX", quantity) values (1, 100), (2, 110), (3, 10); -- --work on veiws create or replace view inventory ("INDEX", quantity, price, total_cost) as --get the total number of units sold with total_units_sold as ( select sum(quantity) total_units_sold from example_db.fifo.sell ), --cumulative_sum table as: the inventory table with a cumulative sum cost column cumulative_sum as ( select *, sum(quantity) over (partition by null order by "INDEX") cumulative_sum from example_db.fifo.buy --do a cross join to add total_units_sold as a new column cross join total_units_sold ), updated_quantity as ( select *, cumulative_sum-total_units_sold buy_sell_difference, --whatever is left over from the buy-sell difference needs to be subtracted iff(buy_sell_difference > 0 and lag(buy_sell_difference) over (partition by null order by "INDEX") < 0, buy_sell_difference, quantity) updated_quantity from cumulative_sum ) select "INDEX", updated_quantity quantity, price, updated_quantity*price total_cost from updated_quantity --purchases that get completely sold out with have a negative buy sell difference, so get removed from the inventory where buy_sell_difference >0 ; --cost of goods sold create or replace view cogs ("INDEX", cogs) as with cumulative_units_sold as ( select *, sum(quantity) over (partition by null order by "INDEX") cumulative_sell from example_db.fifo.sell ), --work out the total amount of stock sold as of the previos sale previous_sales as ( select sell.index sell_index, sell.quantity sell_quantity, buy.index buy_index, buy.quantity buy_quantity, buy.price buy_price, cumulative_sell - sell_quantity sold_previosly, --deal with the sold previosly first then subtract the remainder sum(buy_quantity) over (partition by sell_index order by buy_index) cumulative_buy, --how much is left in the st cumulative_buy-sold_previosly buy_sell_difference from cumulative_units_sold sell full outer join buy order by sell_index, buy_index ), --update the stocks to what we would have after the previous sale --rows with negative (or 0) buy-sell differences are removed in the next table updated_inv_quantity as ( select sell_index, sell_quantity, buy_index, buy_quantity, buy_price, buy_sell_difference, --remaining_stock_rank will be 1 for any partially remaining stock. which will come after some of the prev or same rows have used up stock rank() over (partition by sell_index order by iff(buy_sell_difference

[@dotpi5907]

I'm open to more questions and making another video if that would be useful

[@pprathamesh]

@@dotpi5907 Thank you so much! Truly appreciate!🙏

[@vardhanreddy435]

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

[@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.

[@EmilyLiu-r2p]

So helpful. Thanks!

[@SalmanKhan79]

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

[@satyammarkam775]

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

[@dotpi5907]

Hi Satyam. Yes I've just got that for a query I'm running too. It turns out Snowflake can only store variables up to 225 characters long, so if your pivot column list is longer than that this approach won't work. I'll get back to you once I have the solution

[@dotpi5907]

Hi @Satyam Markam, give this a go kzbin.info/www/bejne/fKmxdX2aj7elmas This video gets around by character restrictions

[@masheTyMasha]

Please make the text bigger..

[@dotpi5907]

Thanks for the feedback, ill be sure to do that for the next videos

[@assafhaim6982]

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).

[@dotpi5907]

Hi Assaf, thanks for the comment. Yes I realized this the other day. When Snowflake stores a variable it is limited to 256 characters. So if the letters and spaces in your column list are more than 256 characters long, then this won't work. Finding a workaround for this is on the list.

[@ravisharma-kh2bq]

@@dotpi5907 Hi did you find work around for this ?

[@dotpi5907]

@@ravisharma-kh2bq sorry for the late reply! I should have something working by for the next coming weekend. I've got a different approach now that looks like this (as a start) execute immediate $$ declare column_list string; final_sql string; res resultset; begin column_list := '(\'BA\', \'AB\', \'AC\')'; final_sql := 'select * from EXAMPLE_TABLE pivot(sum("VALUE") for CATEGORY in ' || column_list || ')'; res := (execute immediate :final_sql); return table(res); end $$; It still needs a bit of work to make the column_list dynamic, but after a bit of testing it looks like the column list can be longer than 256 characters, so that's positive. I'll reply with some updated SQL if i get the solution before this weekend.

[@dotpi5907]

@@ravisharma-kh2bq sorry for the late reply! I should have a different method ready for this coming weekend. Here is the work in progress (below), you might be able to work with that. The column_list still needs to be made dynamic, so there is a bit of work to do. I'll reply to this with the solution if I figure it out before this weekend. execute immediate $$ declare column_list string; final_sql string; res resultset; begin column_list := '(\'BA\', \'AB\', \'AC\')'; final_sql := 'select * from EXAMPLE_TABLE pivot(sum("VALUE") for CATEGORY in ' || column_list || ')'; res := (execute immediate :final_sql); return table(res); end $$;

[@ravisharma-kh2bq]

@@dotpi5907 Thanks alot for your help ! :)

[@jkim8679]

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

[@dotpi5907]

Hey K Kim, yep you're onto it. You could use this method in a few different ways

[@creedz-gvt3987]

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

[@dotpi5907]

Hi @creedz-gvt3987, sorry for the very late reply. You can do this by setting the names of all the columns after pivoting. I've just made a new video on how you can do this dynamically kzbin.info/www/bejne/l4PVZ4WfYq2Np5o

[@benhiggs8834]

Cheers!

[@dotpi5907]

No worries!

[@keshavamugulursrinivasiyen5502]

good use case.

[@dotpi5907]

Thanks Keshava!