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)
Dynamic Column Generation by using pivot [message #571557] Tue, 27 November 2012 01:38 Go to next message
saipradyumn
Messages: 175
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
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
Re: Dynamic Column Generation by using pivot [message #571566 is a reply to message #571562] Tue, 27 November 2012 02:40 Go to previous messageGo to next message
saipradyumn
Messages: 175
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
Re: Dynamic Column Generation by using pivot [message #571568 is a reply to message #571566] Tue, 27 November 2012 02:58 Go to previous message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is one of my packages.
It can't be done with Oracle provided or built-in ones.

Regards
Michel
Previous Topic: Finding the dependency in Oracle
Next Topic: Masking in Oracle
Goto Forum:
  


Current Time: Sat Apr 19 01:14:10 CDT 2014

Total time taken to generate the page: 0.13425 seconds