dynamic number of columns
probably one of the most frequently asked question, you want to have a table like
ACCOUNTING OPERATIONS RESEARCH SALES
ANALYST 0 0 2 0
CLERK 1 0 2 1
MANAGER 0 0 1 1
PRESIDENT 1 0 0 0
SALESMAN 0 0 0 4
but the number and name of columns must be dynamic.
typically, I answer : this is not possible in plain sql. you need to use plsql.
well. It is not too much beautifoul, but I have a suggestion
step one:
generate the select
spool crosstab.sql
select text from (
select 1 i, 'select job' text from dual
union all
select 2+rownum, ', count(decode(deptno,'||deptno||',deptno)) '||dname from
(select deptno,dname from dept order by dname)
union all
select 1e125, ' from emp group by job order by job;' from dual
)
order by i
/
spool off
step two:
execute the generated query
@crosstab
JOB ACCOUNTING OPERATIONS RESEARCH SALES
--------- ---------- ---------- ---------- ----------
ANALYST 0 0 2 0
CLERK 1 0 2 1
MANAGER 1 0 1 1
PRESIDENT 1 0 0 0
SALESMAN 0 0 0 4
done!
ACCOUNTING OPERATIONS RESEARCH SALES
ANALYST 0 0 2 0
CLERK 1 0 2 1
MANAGER 0 0 1 1
PRESIDENT 1 0 0 0
SALESMAN 0 0 0 4
but the number and name of columns must be dynamic.
typically, I answer : this is not possible in plain sql. you need to use plsql.
well. It is not too much beautifoul, but I have a suggestion
step one:
generate the select
spool crosstab.sql
select text from (
select 1 i, 'select job' text from dual
union all
select 2+rownum, ', count(decode(deptno,'||deptno||',deptno)) '||dname from
(select deptno,dname from dept order by dname)
union all
select 1e125, ' from emp group by job order by job;' from dual
)
order by i
/
spool off
step two:
execute the generated query
@crosstab
JOB ACCOUNTING OPERATIONS RESEARCH SALES
--------- ---------- ---------- ---------- ----------
ANALYST 0 0 2 0
CLERK 1 0 2 1
MANAGER 1 0 1 1
PRESIDENT 1 0 0 0
SALESMAN 0 0 0 4
done!
3 Comments:
"step one:
generate the select
step two:
execute the generated query"
Haha - every problem is this simple! :)
when i run this query on my sql (oracle 9.2.0.1.0
it says:
1 select text from (
2 select 1 i, 'select job' text from dual
3 union all
4 select 2+rownum, ', count(decode(deptno,'deptno',deptno)) 'dname from
5 (select deptno,dname from dept order by dname)
6 union all
7 select 1e125, ' from emp group by job order by job;' from dual
8 )
9* order by i
SQL> /
select 2+rownum, ', count(decode(deptno,'deptno',deptno)) 'dname from
*
ERROR at line 4:
ORA-00923: FROM keyword not found where expected
whats the problem??
don't ask why, but the || disappear from my post... I really need to learn how to use blogger more efficiently. I corrected it, please try again
Post a Comment
<< Home