Home » SQL & PL/SQL » SQL & PL/SQL » Pivot (11 g)
Pivot [message #568139] Tue, 09 October 2012 12:46 Go to next message
primer2020
Messages: 32
Registered: August 2012
Member
Hello,
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 let 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 #568140 is a reply to message #568139] Tue, 09 October 2012 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't. It is not supported in current version (unless you accept an XML result).

Regards
Michel
Re: Pivot [message #568144 is a reply to message #568139] Tue, 09 October 2012 13:04 Go to previous messageGo to next message
primer2020
Messages: 32
Registered: August 2012
Member
Thank you...
Re: Pivot [message #568145 is a reply to message #568140] Tue, 09 October 2012 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually it is possible using cartridge interface which allows you to enter into the parsing engine:
SQL> select * from table(pkg_pivot.pivot(
  2  '(SELECT * FROM x) PIVOT (SUM (b) FOR yr IN (ANY ASC))'
  3  )) order by a
  4  /
         A       2000       2001       2002       2003
---------- ---------- ---------- ---------- ----------
         1          2          2          2          2
         8          9          9          9          9

2 rows selected.

SQL> @v

Version Oracle : 10.2.0.4.0

Regards
Michel

[Updated on: Tue, 09 October 2012 13:23]

Report message to a moderator

Re: Pivot [message #568149 is a reply to message #568144] Tue, 09 October 2012 13:43 Go to previous messageGo to next message
primer2020
Messages: 32
Registered: August 2012
Member
Is pkg_pivot is a system predefined package ?
It did not work for me...
Re: Pivot [message #568155 is a reply to message #568149] Tue, 09 October 2012 23:41 Go to previous messageGo to next message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not, it is one I wrote upon cartridge API.

Regards
Michel
Re: Pivot [message #568292 is a reply to message #568155] Wed, 10 October 2012 09:34 Go to previous message
primer2020
Messages: 32
Registered: August 2012
Member
Hi, Can I see the code about the pivot function ?

Thank you
Previous Topic: Preventing concurrent running of a stored procedure
Next Topic: Create multiple records based on condition
Goto Forum:
  


Current Time: Sat Dec 20 03:54:54 CST 2014

Total time taken to generate the page: 0.10473 seconds