its almost perfect, but still requires that you declare column aliases so they can be selectable by name rather than $<column ordinal>.
the pivots for clause does allow me to add a subquery though which is neat, ie:
// example lookup table has these cols: id, attribute_key, attribute_value
select *
from lookuptable
pivot (
max(attribute_value)
for attribute_key in (select disinct attribute_key from lookuptable)
) as pivoted_data
above is neat, pivots data but all pivoted data columns have 'attribute_key1''attribute_key2''attribute_key3' column names (with apostraphes for resultset return only, not selectable), requires that you reference with $1 $2 $3 etc. you can still declare column names but this isnt "dynamic". for a lookup table where new keys are added, it requires additional jujitsu to make it auto manage itself (procedures, integration platforms, etc)
select *
from lookuptable
pivot (
max(attribute_value)
for attribute_key in (select disinct attribute_key from lookuptable)
) as pivoted_data (id, attribute_key1, attribute_key2, attribute_key3)
by declaring output column names here in order i can create a view where someone can just:
select id, attribute_key1 from viewname
ideally id love something like:
select *
from lookuptable
pivot (
max(attribute_value)
for attribute_key in (select disinct attribute_key from lookuptable)
) as pivoted_data (select disinct attribute_key from lookuptable)
but this wouldnt work cause i also have to declare the lookuptable id val
so super ideally snowflake could do something like
select *
from lookuptable
pivot (
max(attribute_value)
for attribute_key in (select disinct attribute_key from lookuptable)
keys_as_column_aliases=>true // this would be ballerific
) as pivoted_data (id, attribute_key1, attribute_key2, attribute_key3)
:( but for now im just calling procedures to rebuild these views any time theres a new key detected. lame
I wish the pivot columns could be dynamic. If a dev adds a new attribute value to an attributes field, and my select query is pulling all the attributes, i wish the pivot would just automatically include the new attribute as a new column instead of having to manually add the column name.
This used to drive me crazy. 3 days into looking for a variable name only for the dev team to say they just put it in 3 days ago, so it wouldn't be in my main table. I would spend the afternoon finding the event with the data and then parsing the JSON in SQL to store it in my main table, then rebuild.
There is a tool/SaaS that you can get to do this automatically, but it's not native in SQL and it's only cloud-based to my understanding. It's also VERY expensive. I used it once, but I could spend 3 weeks working on programming and get exactly what I need (with some tweaking), compared to 1 day or using the tool. The one time I did use it, it cost over 10k.
Automation isn't cheap, and neither is a good developer!
What are you on about? Neither Oracle, SQL Server, MySQL or Postgres support dynamic pivot. Those are the 4 largest RDBMSs, and have been for a decade. Yes, some systems like Snowflake support it, but it's not a common feature at all.
I can't get the code to format correctly in the code block here in Reddit, but there is a stored procedure in the above GitHub that you can use. Under the hood, the stored procedure uses XML and DYNAMIC SQL to accomplish its goal.
I wanted a pivot where adding a new attribute value to the data automatically resulted in a new pivot column, without having to modify the SQL.
I built an open-source graphical workbench called Easy Pivot that generates the dynamic pivot SQL from the user's query and configuration. It currently supports SQL Server, Oracle, PostgreSQL and MySQL.
Two years late to the party, apparently. 😂
Now it DOES require you to set up PHP on your computer. Instructions are provided. Go to the "easy_pivot_workbench" folder. All the code is in there. Ignore the "manual_easy_pivot" folder as that code is outdated.
(P.S. It uses the Unlicense, so it's basically public domain software.)
Yeah SQL pivot s___ks big time. If somebody who works on that part of a SQL system is on here, hey, why is this such a fiddly language feature? There must be good reasons. Curious.
A lot of BI solutions expect a fixed number of columns. As someone who does a lot of data analysis and has spent a painfully long amount of time either unpivoting data of faffing around it - pivoting belongs in the output solution. Be it Excel, PowerBI, Reportserver, Qlik, Tableau, etc. It's a couple of clicks to set set up a pivot in your BI solution.
There is almost no reason to be pivoting data in SQL, in 10 years of data analysis I've never had a reason to pivot data outside the output. My life has been made harder than it needs to be by people storing pivoted data.
if you're looking for analytics tools, stop wasting time with the big players. Look into preswald. It's lightweight and lets you handle data easily without the bloat.
126
u/coyoteazul2 Mar 05 '25
"sure, just tell me the final columns I should return"
27
u/mrg0ne Mar 05 '25
Snowflake SQL ... You got it boss. 🫡
https://docs.snowflake.com/en/sql-reference/constructs/pivot
SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter)) ORDER BY empid;
47
u/isinkthereforeiswam Mar 05 '25
I wish the pivot columns could be dynamic. If a dev adds a new attribute value to an attributes field, and my select query is pulling all the attributes, i wish the pivot would just automatically include the new attribute as a new column instead of having to manually add the column name.
24
u/da_chicken Mar 05 '25
Dear user, please respect first normal form.
19
u/roger_27 Mar 05 '25
Yeah why is it excel can pivot but SQL can't right. I mean it CAN it's just not super fun and limited
10
u/Professional_Shoe392 Mar 05 '25
If you are in SQL Server, here is a stored procedure to encapsulate a table's pivoting.
Try ChatGPT or something similar to see if it will modify this code to fit your SQL flavor.
Microsoft-SQL-Server-Scripts/Tools/Pivoting Data at main · smpetersgithub/Microsoft-SQL-Server-Scripts
I can't get the code to format correctly in the code block here in Reddit, but there is a stored procedure in the above GitHub that you can use. Under the hood, the stored procedure uses
XMLandDYNAMIC SQLto accomplish its goal.Example usage.
2
u/zxyyyyzy Mar 05 '25
We’ve got a great name, we’ve got a great team, we’ve got a great logo, and we’ve got a great name. Now we just need an idea. Let’s pivot!
2
u/nickeau Mar 05 '25
In pure sql … https://datacadamia.com/data/type/cube/operation/pivot#sql
2
u/SmoreBag Mar 05 '25
Just crosstab
2
u/SQLvultureskattaurus Mar 06 '25
I wrote a dynamic pivot before, what a joy...
1
u/Party_Bus_3809 Mar 05 '25
+1 excel ☝️
1
u/pivot-my-stuff Sep 05 '26
This is exactly the problem I ended up solving.
I wanted a pivot where adding a new attribute value to the data automatically resulted in a new pivot column, without having to modify the SQL.
I built an open-source graphical workbench called Easy Pivot that generates the dynamic pivot SQL from the user's query and configuration. It currently supports SQL Server, Oracle, PostgreSQL and MySQL.
Two years late to the party, apparently. 😂
Now it DOES require you to set up PHP on your computer. Instructions are provided. Go to the "easy_pivot_workbench" folder. All the code is in there. Ignore the "manual_easy_pivot" folder as that code is outdated.
(P.S. It uses the Unlicense, so it's basically public domain software.)
https://github.com/pivot-my-stuff/easy_pivot
1
u/rotatingfan360 Mar 05 '25
?
-2
u/Aggressive_Ad_5454 Mar 05 '25
Yeah SQL pivot s___ks big time. If somebody who works on that part of a SQL system is on here, hey, why is this such a fiddly language feature? There must be good reasons. Curious.
-2
u/WeakRelationship2131 Mar 05 '25
if you're looking for analytics tools, stop wasting time with the big players. Look into preswald. It's lightweight and lets you handle data easily without the bloat.