OraFAQ Forum: SQL & PL/SQL » Dynamic Column Generation by using pivot

Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Column Generation by using pivot (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Dynamic Column Generation by using pivot [message #571557]

Tue, 27 November 2012 01:38

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Michel,

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

[Updated on: Tue, 27 November 2012 01:45] by Moderator

Report message to a moderator

Re: Dynamic Column Generation by using pivot [message #571559 is a reply to message #571557]

Tue, 27 November 2012 01:43

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Keep your lines of code in 80 character width: no more than 80 characters on each line.
Remove useless lines, we don't care of 10 Lines with only "SQL> ".
And trim the spaces at the end of the lines.

It is currently not supported.

Regards
Michel

[Updated on: Tue, 27 November 2012 01:45]

Report message to a moderator

Re: Dynamic Column Generation by using pivot [message #571562 is a reply to message #571559]

Tue, 27 November 2012 02:12

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Dynamic Column Generation by using pivot [message #571566 is a reply to message #571562]

Tue, 27 November 2012 02:40

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Michel ,

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

Report message to a moderator