Dynamic Pivot Columns | How to generate dynamic Pivot Columns in BigQuery

SQL Code available at : datasudh.com/b...

[@shivarajmiraje6672]

Really helpful 🎉

[@agustinnosiglia5848]

Exactly what I was looking for with an easy-to-understand example! Thanks!

Awesome! Congrats for the videos! Keep posting to the BigQuery series please!!! Cheers

[@yuejian8089]

Thank you!!!! It reallly helps a lot.

[@TechnologiesExtendo]

Thank you much for the tutorial! Just what I need it. Now, how can you create an new table from the results in a way you can do it scheduled? I believe that would be using STORE PROCEDUREs with this scripting, but I don't know what would be the syntax to store the results into a final TABLE that can be reconstructed every time I run the PROCEDURE. I tried using SCHEDULED QUERIES but the scripting is not allowed there. Any help would be much appreciated.

[@user-vy1py7io7s]

Great Video!

[@hasanasim3980]

Thank you for the wonderful explanation. Is there a similar way by which we can dynamically aggregate string columns in bigquery sql? Usually we would just use Select STRING_AGG(col_1), col_2 From table1 Group BY col_2. What if the number of columns are increasing dynamically?

[@datasudh]

Hi Hasan, if you have a scenario where in you want to keep creating aggregate values for dynamic columns which can exist at any point of time then basically you need a query which adds that column formula at run time. You can achieve that by using combination of metadata table which will tell about new column and then add the same to dynamic SQL with string agg function. Do you have a use case where in your columns are increasing dynamically ?

[@hasanasim3980]

@@datasudh I have a ‘contacts’ table in which new columns (containing information about the contacts like their age etc.) are being added everyday. These columns have either nulls or string values. I want to string aggregate all these columns and group by contact id so that I get one unique row per contact ID.

[@hasanasim3980]

@@datasudh thanks for your reply. I have been trying to use INFORMATION_SCHEMA.COLUMNS with dynamic sql to solve this problem but have been unsuccessful so far.

[@datasudh]

@@hasanasim3980 ok, I will give this a try this week and share results with a blog

[@alelust7170]

Tks a lot!

[@ClaudioDeLucke]

How could i connect this to sheets?

[@lxvi4322]

Thansk, can I use this with a CTE?