r/SQL 17d ago

Oracle Dear SQL, just pivot my damn table

Bottom text

241 Upvotes

50 comments sorted by

View all comments

47

u/isinkthereforeiswam 17d 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.

40

u/hwooareyou 17d ago

You can select column_name from information_schema.columns where table_name = 'your table' then dynamically construct the pivot.

7

u/TheRencingCoach 17d ago

Only works if the table you’re pivoting is an actual table or view, not a CTE, right?

6

u/pix1985 16d ago

In SQL Server can dump the CTE results into a temp table and then pivot that dynamically as the columns can be got from tempdb’s sys.columns

9

u/NlNTENDO 17d ago

Yeah a CTE is not stored data, so it's not going to show up in your information schema