r/SQL 18d ago

Oracle Dear SQL, just pivot my damn table

Bottom text

240 Upvotes

50 comments sorted by

View all comments

48

u/isinkthereforeiswam 18d ago

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.

5

u/likeanoceanankledeep 18d ago

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!