shvanwijlen
asked on
Dynamic matrix SQL
Hi, I know the standard EMP matrix query via a decode, but I'd like a SQL that is dynamic with respect to the columns.
Using the EMP example, in my case I don't want to re-code the query once a new department is created.
We're on Oracle 9i. Isn't there a new [data warehousing] clause that allows me to do this?
Thanks,
Simon
Using the EMP example, in my case I don't want to re-code the query once a new department is created.
We're on Oracle 9i. Isn't there a new [data warehousing] clause that allows me to do this?
Thanks,
Simon
As far as I know You need to write some PL/SQL code that will find distinct values (i.e. departments) from desired column and dynamically generate query for You.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This would work, but is there not a way to accomplish this via the "newer" functions such as rollup, cube, grouping sets?
I would say no because when You write select statement You need to know how many columns it will return - and this proves that there's no non-dynamic technique that will do the job for You (until You have some limitation on number of columns).
no such thing to specify dynamic columns in sql itself till now. You need to do it by yourself.