Categories
Blogroll sql

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!

By Laurent Schneider

Oracle Certified Master

5 replies on “dynamic number of columns”

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

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 Reply

Your email address will not be published.