Is there any way to generate columns dynamically by depending on the rows in a table in 11G .

Ex: If the deptno in DEPT table is not constant,then how to generate the N numbers of columns based on the deptno.

Below query is working when we hard coded the deptno (10,20,30,40).What else if we more number of departments and we don't know the departments also.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as dbo SQL> SELECT * FROM (SELECT deptno, job, sum(sal) sal FROM SCOTT.emp GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN(10, 20, 30, 40)); JOB 10 20 30 40 --------- ---------- ---------- ---------- ---------- CLERK 1300 1900 950 SALESMAN 5600 PRESIDENT 5000 MANAGER 2450 2975 2850 ANALYST 6000

I just tried by the following way.Second query was not working ,where as first one was working fine.

SQL> select * 2 from scott.dept sd 3 where sd.deptno in (select distinct deptno from scott.emp); DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO SQL> SELECT * 2 FROM (SELECT deptno, job, sum(sal) sal F ROM SCOTT.emp GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN (select distinct deptno 3 from scott.emp)); SELECT * FROM (SELECT deptno, job, sum(sal) sal FROM SCOTT.emp GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN (select distinct deptno from scott.emp )) ORA-00936: missing expression

Please help me

Thanks in advance

SaiPradyumn ]]>

And trim the spaces at the end of the lines.

It is currently not supported.

Regards

Michel

]]>

SQL> select * 2 from table(pkg_pivot.pivot( 3 '(select deptno, sal, job from EMP) 4 pivot (sum(sal) sal 5 for (deptno) in (select distinct deptno from emp order by 1))' 6 )) 7 order by job 8 / JOB SAL_10 SAL_20 SAL_30 --------- ---------- ---------- ---------- ANALYST 6000 CLERK 1300 1900 950 MANAGER 2450 2975 2850 PRESIDENT 5000 SALESMAN 5600]]>

Thanks for your quick response.But still I am getting the following exception ,

PKG.PIVOT is this Oracle provided package or User defined Package?

select * 2 from table(pkg_pivot.pivot( 3 '(select deptno, sal, job from EMP) 4 pivot (sum(sal) sal 5 for (deptno) in (select distinct deptno from emp order by 1))' 6 )) 7 order by job SQL> / select * from table(pkg_pivot.pivot( '(select deptno, sal, job from EMP) pivot (sum(sal) sal for (deptno) in (select distinct deptno from emp order by 1))' )) order by job ORA-00904: "PKG_PIVOT"."PIVOT": invalid identifier

Thanks

SaiPradyumn ]]>

Regards

Michel

]]>

Please provide pkg_pivot.pivot code.]]>