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!

Published by Laurent Schneider

Oracle Certified Master

Join the Conversation

5 Comments

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

  2. 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

Leave a comment

Your email address will not be published.