Link to home
Start Free TrialLog in
Avatar of shvanwijlen
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
Avatar of GGuzdziol
GGuzdziol
Flag of Luxembourg image

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
Avatar of Acton Wang
Acton Wang
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of shvanwijlen
shvanwijlen

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.