• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1817
  • Last Modified:

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
0
shvanwijlen
Asked:
shvanwijlen
  • 2
  • 2
1 Solution
 
GGuzdziolCommented:
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.
0
 
actonwangCommented:
you could construct dynamic sql on the fly and "execute immediate" to return the result.

here is the example:

http://asktom.oracle.com/pls/ask/f?p=4950:8:1942777812723534670::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:15151874723724
0
 
shvanwijlenAuthor Commented:
This would work, but is there not a way to accomplish this via the "newer" functions such as rollup, cube, grouping sets?
0
 
GGuzdziolCommented:
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).
0
 
actonwangCommented:
no such thing to specify dynamic columns in sql itself till now. You need to do it by yourself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now