[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1811
  • 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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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