| Pivot [message #568139] |
Tue, 09 October 2012 12:46  |
 |
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 #568145 is a reply to message #568140] |
Tue, 09 October 2012 13:04  |
 |
Michel Cadot
Messages: 54165
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 |
|
|
|
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
