This video is awesome, awesome, awesome! It is approachable for someone not very comfortable with python (yet-but I am trying) and it was very clear how to apply to the viewer’s specific use case. I find other videos from different creators sometimes fixate too much on the example and it makes it harder to translate to your own needs. Question though for expansion of this - how would you go about doing this with two variables, for example? Say I want 2 columns for every category - one that has “value” as the summed value and one that has another column, either the count of values aggregated for that result or a sum of something else, like value2.
Hi @jessicalenoir9979, sorry that was much longer than a few days. I've finally got a solution, here it is th-cam.com/video/aRr6Ri-HZq8/w-d-xo.html Let me know if you have any other questions.
Is it possible to select from the returned result set? ChatGPT told me to 'select * from table(dynamic_pivot())' . However, this query threw me the error 'Unknown Function:Dynamic_Pivot'. The stored procedure has been created successfully and there was no issue if it got direct call. My current solution is adding one more statement to create a temporary table in the stored procedure to store the returned result set. Wondering if Snowflake supports direct select from the result of a stored procedure at all.
Hi @chaloemkietchairat5709, if you want to use it in a CTE you might be better of making dynamic_pivot a user defined function rather than a stored procedure, see the difference here (docs.snowflake.com/en/developer-guide/stored-procedures-vs-udfs). I didnt know so much about user defined functions when I made this video. Hope that works for you!
@@dotpi5907@chaloemkietchairat5709 I want to call dynamic pivot within a view. UDF is right place to go but not sure how to create one as it got its own limitations. Any video on that would be super helpful for the whole community.
Don't get me wrong, this video is amazing and thank you very much, however, I still do not understand why SF would not allow using subquery or other option to do this. I am struggling for 2nd day now to finish task at work and I am constantly amazed by how annoying SF is. Right now I can't get pivot to work, because I can't select column that I am using in PIVOT because it throws invalid identifier. I think I will work towards SF certification, because even chat GPT can't help me at work so it would be a safe bet (at least for now)
Hi @Jolbulka, yep I thought the same thing when I was trying to figure this out. If you're still stuck you can send through some details on your problem to the dot-pi contact page dot-pi.com/contact/ I might be able to help out over the next few days
Hi Kaali, thank you for commenting. The - SHOW PROCEDURES command should list all the stored procedures that you have in Snowflake. Try running that to see if the stored procedure you created has definitally been saved in Snowflake. Let me know how you get on
Thank you very much for the video, very clear and much needed information
Thanks heaps! Glad it helped
Thanks man amazing content keep going !
Thanks for the support!
This video is awesome, awesome, awesome! It is approachable for someone not very comfortable with python (yet-but I am trying) and it was very clear how to apply to the viewer’s specific use case. I find other videos from different creators sometimes fixate too much on the example and it makes it harder to translate to your own needs.
Question though for expansion of this - how would you go about doing this with two variables, for example? Say I want 2 columns for every category - one that has “value” as the summed value and one that has another column, either the count of values aggregated for that result or a sum of something else, like value2.
Hi @jessicalenoir9979, thanks heaps for the really nice comment, thats great to hear!
Great question! I'll make a video on that in the next few days
Hi @jessicalenoir9979, sorry that was much longer than a few days. I've finally got a solution, here it is th-cam.com/video/aRr6Ri-HZq8/w-d-xo.html
Let me know if you have any other questions.
Awesome video thanks . Do you have video to load data into Snowflake Table from Excel file placed in Server , by using Python? Thanks
Thanks @ravi sharma, i dont have a video for that, but its a good idea! I'll add it to the list
Is it possible to select from the returned result set? ChatGPT told me to 'select * from table(dynamic_pivot())' . However, this query threw me the error 'Unknown Function:Dynamic_Pivot'. The stored procedure has been created successfully and there was no issue if it got direct call. My current solution is adding one more statement to create a temporary table in the stored procedure to store the returned result set. Wondering if Snowflake supports direct select from the result of a stored procedure at all.
I try to CALL the dynamic_pivot in the CTE and it's doesn't work. Do you know why and is there any workaround?
Hi @chaloemkietchairat5709, if you want to use it in a CTE you might be better of making dynamic_pivot a user defined function rather than a stored procedure, see the difference here (docs.snowflake.com/en/developer-guide/stored-procedures-vs-udfs).
I didnt know so much about user defined functions when I made this video. Hope that works for you!
Thank you so much @@dotpi5907
@@dotpi5907@chaloemkietchairat5709 I want to call dynamic pivot within a view. UDF is right place to go but not sure how to create one as it got its own limitations. Any video on that would be super helpful for the whole community.
Don't get me wrong, this video is amazing and thank you very much, however, I still do not understand why SF would not allow using subquery or other option to do this. I am struggling for 2nd day now to finish task at work and I am constantly amazed by how annoying SF is. Right now I can't get pivot to work, because I can't select column that I am using in PIVOT because it throws invalid identifier. I think I will work towards SF certification, because even chat GPT can't help me at work so it would be a safe bet (at least for now)
Hi @Jolbulka, yep I thought the same thing when I was trying to figure this out.
If you're still stuck you can send through some details on your problem to the dot-pi contact page dot-pi.com/contact/ I might be able to help out over the next few days
When I am running the same Stored procedure query, Its saying "Unknown Function:Dynamic_Pivot
Can you tell me why??
Hi Kaali, thank you for commenting.
The - SHOW PROCEDURES command should list all the stored procedures that you have in Snowflake. Try running that to see if the stored procedure you created has definitally been saved in Snowflake. Let me know how you get on
@@dotpi5907 Actually I have not created any procedure in past, So for the above condition what I should create and how?