MATRIX SQL QUERY

[thebull1]

Hi All?
I am working on a matrix query whereby I do not want to specify the columns

ie
Select job_id, (row 1..n of table emp should be the columns) from emp;

Old way
------------
Select job_id, decode (dept id,10) "Dept 10",...
decode (dept id,n) "Dept n",

from emp;

Basically you have an arbitrary table emp. This table contains employee details including department code. I Want to group some data by department

Eg the final report will be Job title Dept A, Dept B, Dept C, ..., Dept z
imagine you have 1000 departments you do not want to use the decode command to pick each department since this will mean writing 1000 decode statements.

I want to write on single matrix query wihout using the decode statement.
Kindly assist.

 

[SantaMufasa]

So, Bull,

I have a few questions for you before we set about trying to resolve your need:

1) Since your sample DECODE statement is malformed, I cannot infer what data you want to display for each Dept; you said "I Want to group some data by department", yet I see no GROUP BY clause. Please offer a more concrete example of what you want to display for each Dept.

3) What ORDER do you want for your "columns" of Dept data?...Ascending...Descending...Random?

4) Will each Job_ID have 1000 Departments?...Might a Job_id have zero Departments? Might a Job_id have only one Department? If so, might/should it appear in the 1000th column position?...Should the same Department ID for two different Job_IDs appear in the same column of your report?

5) If you are unwilling to specify a DECODE function for each column, what are you willing to specify? For example, are you willing to code this function: "x(1)", "x(2)", etc cetera?

6) You said,

...imagine you have 1000 departments...

I can imagine that I have 1000 departments, but I certainly cannot imagine wanting to display their data in 1000 columns. Can you please propose a business scenario where seeing 1000 columns of data is preferrable to seeing 1000 rows of data?...And how the business could justify the machine cycles of transposing the data from rows to columns. Specifically, can you show how the business's expenses will decrease or its revenue increase by investing all of the human and machine time to transpose your data? Why are your human users able to function more effectively if they see the data in columns than if they see the data in rows?


Looking forward to your clarifications.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[thebull1]

Thanks SantaMufasa.
I will rephrase my question with a better example, then aks my question:-

In the example below, we take the employee name (ename) and the department number (deptno) and display the salary of the employee by their department.
SELECT * FROM
(SELECT
ename,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40
FROM emp
GROUP BY ename)
ORDER BY 1;

The expected result would be as sampled below:-

ENAME DEPT10 DEPT20 DEPT30 DEPT40

---------- ---------- ---------- ---------- ----------
ADAMS 1100
ALLEN 1600
BLAKE 2850
CLARK 2450
..........etc

I would wish to make the Select a dynamic one, with resepect to the departments i.e. I envisage a situation where the Departments increase for example, or are merged for that matter, in which case now my Decode will no longer be accurate. Can I do a Select that has dynamic columns for the departments??

Rgds

 

[SantaMufasa]

Yes, you/we can compose a dynamic SQL statement for either display purposes or to create a VIEW. My problem is that I must leave for a commitment soon and don't have the time to code a sample solution presently.

In anticipation of building a solution, could you please confirm whether you want a dynamic display or a dynamic VIEW?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[thebull1]

Hi there!
Thanks for your speedy response. Either view or display will do. You can choose the easier one.

Thanks, in anticipation too