Oracle 10G - Dynamic Decode Function - how is it possible ?

[nkshah]

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??


Let me know if you need more information.

Thanks.

 

[SantaMufasa]

NK,

Here is a solution that creates SQL code dynamically to accommodate any number of department columns. (In fact, in my solution, below, I arbitrarily limited the deptartments to "<= 41" so that the output would fit horizontally on a Tek-Tips page without wrapping.) The presumption is that you are using SQL*Plus for the solution. Another presumption is that you run the code from a script so that SQL*Plus will not "echo" the SQL code to the screen. (In my case, I saved the following code to a script named "tt_598.sql".)

Contents of "tt_598.sql":

set echo off
set pagesize 0
set feedback off
spool temp.sql
prompt set pagesize 35
col last_name format a12
select distinct 'col DEPT'||dept_id||' format 9,999'
  from s_emp
 where dept_id <= 41;
SELECT 'select * from (select LAST_NAME' from dual;
select distinct ',sum(decode(dept_id,'||dept_id||',salary)) dept'||dept_id
  from s_emp
 where dept_id <= 41
 order by 1;
select 'from s_emp  where dept_id <= 41 group by last_name) order by 1;' from dual;
spool off
@temp

Invocation of "tt_596.sql" from a SQL*Plus prompt:

SQL> @tt_598
set pagesize 35
col DEPT41 format 9,999
col DEPT32 format 9,999
col DEPT33 format 9,999
col DEPT10 format 9,999
col DEPT31 format 9,999
col DEPT34 format 9,999
col DEPT35 format 9,999
select * from (select LAST_NAME
,sum(decode(dept_id,10,salary)) dept10
,sum(decode(dept_id,31,salary)) dept31
,sum(decode(dept_id,32,salary)) dept32
,sum(decode(dept_id,33,salary)) dept33
,sum(decode(dept_id,34,salary)) dept34
,sum(decode(dept_id,35,salary)) dept35
,sum(decode(dept_id,41,salary)) dept41
from s_emp  where dept_id <= 41 group by last_name) order by 1;

LAST_NAME    DEPT10 DEPT31 DEPT32 DEPT33 DEPT34 DEPT35 DEPT41
------------ ------ ------ ------ ------ ------ ------ ------
Dumas                                            1,450
Giljum                      1,490
Maduro                                                  1,400
Magee                1,400
Nagayama             1,400
Ngao                                                    1,450
Nguyen                                    1,525
Patel                                       795
Quick-To-See  1,450
Sedeghi                            1,515
Smith                                                     940
Urguhart                                                1,200
SQL>

Let us know if this resolves your need.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”