| Pivot [message #570640] |
Tue, 13 November 2012 07:13  |
 |
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 #570646 is a reply to message #570644] |
Tue, 13 November 2012 10:11  |
 |
Michel Cadot
Messages: 54712
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 |
|
|
|
] 
Members
Search
Help
Register
Login
Home
