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!

5 thoughts on “dynamic number of columns

  1. Muhammad Asim

    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. Jennifer J

    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

  3. Laurent Schneider Post author

    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
    )));

Comments are closed.