You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




31 August 2005

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!

3 Comments:

Blogger Robert Vollman said...

"step one:
generate the select

step two:
execute the generated query"

Haha - every problem is this simple! :)

31/8/05 23:59  
Blogger Muhammad Asim said...

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

3/9/05 12:02  
Blogger Laurent Schneider said...

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

3/9/05 20:26  

Post a Comment

<< Home