Home » SQL & PL/SQL » SQL & PL/SQL » Pivot (11.1g)
Pivot [message #570640] Tue, 13 November 2012 07:13 Go to next message
primer2020
Messages: 32
Registered: August 2012
Member
Hello,
I have posted this question before but I was not able to get a partial answer from Michel Cadot

http://www.orafaq.com/forum/m/568139/174420/#msg_568139

Can you please help ? I need a dynamic way to do pivoting.

This code pivot the table x on yr. How ever I have to hard code the IN (2000,2001,2002,2003).
I would like to omit the range, and do it dynamically.

Thank you

CREATE TABLE x
(
a NUMBER,
b NUMBER,
yr NUMBER
);

INSERT INTO x
VALUES (1, 2, 2000);

INSERT INTO x
VALUES (1, 2, 2001);

INSERT INTO x
VALUES (1, 2, 2002);

INSERT INTO x
VALUES (1, 2, 2003);



INSERT INTO x
VALUES (8, 9, 2000);

INSERT INTO x
VALUES (8, 9, 2001);

INSERT INTO x
VALUES (8, 9, 2002);

INSERT INTO x
VALUES (8, 9, 2003);



SELECT *
FROM (SELECT * FROM x) PIVOT (SUM (b) FOR yr IN (2000, 2001, 2002, 2003))
ORDER BY a; 
Re: Pivot [message #570641 is a reply to message #570640] Tue, 13 November 2012 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 58499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not supported.
You can only do it if you return an XML string not the usual tabular format; then your application can convert this XML to a tabular format to display (this is actually the first purpose of XML).

Regards
Michel
Re: Pivot [message #570644 is a reply to message #570640] Tue, 13 November 2012 08:54 Go to previous messageGo to next message
primer2020
Messages: 32
Registered: August 2012
Member
Hi,
You previously said it is possible, but i do not have this package.

select * from table(pkg_pivot.pivot(
 '(SELECT * FROM x) PIVOT (SUM (b) FOR yr IN (ANY ASC))'
  )) order by a

Re: Pivot [message #570646 is a reply to message #570644] Tue, 13 November 2012 10:11 Go to previous message
Michel Cadot
Messages: 58499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it is one of my packages.
SQL> select * from table(pkg_pivot.pivot(
  2   '(SELECT * FROM x) PIVOT (SUM (b) FOR yr IN (ANY ASC))'
  3    )) order by a
  4  
SQL> /
         A       2000       2001       2002       2003
---------- ---------- ---------- ---------- ----------
         1          2          2          2          2
         8          9          9          9          9

What you can do is:
SQL> SELECT *
  2  FROM (SELECT * FROM x) PIVOT xml (SUM (b) FOR yr IN (ANY))
  3  order by a
  4  /
         A
----------
YR_XML
--------------------------------------------------------------------------------
         1
<PivotSet><item><column name = "YR">2000</column><column name = "SUM(B)">2</colu
mn></item><item><column name = "YR">2001</column><column name = "SUM(B)">2</colu
mn></item><item><column name = "YR">2002</column><column name = "SUM(B)">2</colu
mn></item><item><column name = "YR">2003</column><column name = "SUM(B)">2</colu
mn></item></PivotSet>
         8
<PivotSet><item><column name = "YR">2000</column><column name = "SUM(B)">9</colu
mn></item><item><column name = "YR">2001</column><column name = "SUM(B)">9</colu
mn></item><item><column name = "YR">2002</column><column name = "SUM(B)">9</colu
mn></item><item><column name = "YR">2003</column><column name = "SUM(B)">9</colu
mn></item></PivotSet>

Regards
Michel

[Updated on: Wed, 14 November 2012 01:15]

Report message to a moderator

Previous Topic: ORA-00054
Next Topic: Subtract dates
Goto Forum:
  


Current Time: Wed Jul 23 09:28:55 CDT 2014

Total time taken to generate the page: 0.09033 seconds