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!
“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
This was very helpful. Is there a way to combine the two steps into 1 sql statement? For example, I have some Java code that reads a sql statement from a file and then generates an Excel report from executing the sql. Each column returned from the sql statement becomes a column in the report. I’ve tried using functions and stored procedures to return result sets but I can’t figure out how to expand the cursor into the columns in an sql statement.
Thanks,
Jennifer
is xml an option?
Excel can read xml too
then you can use something like
select column_value
from table(xmlsequence(cursor(
select ename from emp where deptno=10
)));