Laurent Schneider

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!

This entry was posted on Wednesday, August 31st, 2005 at 15:04 and is filed under oracle, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

5 Responses to “dynamic number of columns”

Robert Vollman Says:
August 31st, 2005 at 23:59
“step one:
generate the select

step two:
execute the generated query”

Haha - every problem is this simple!
Muhammad Asim Says:
September 3rd, 2005 at 12:02
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??
Laurent Schneider Says:
September 3rd, 2005 at 20:26
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
Jennifer J Says:
September 3rd, 2007 at 13:30
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
Laurent Schneider Says:
September 3rd, 2007 at 13:38
is xml an option?

Excel can read xml too

then you can use something like
```
select column_value 
from table(xmlsequence(cursor(
<b>  select ename from emp where deptno=10</b>
)));
```

Use <code> and </code> to post code

dynamic number of columns

5 Responses to “dynamic number of columns”

Leave a Reply