Home » SQL & PL/SQL » SQL & PL/SQL » pivot query
pivot query [message #545023] |
Sun, 26 February 2012 17:53 |
|
samri
Messages: 18 Registered: January 2012 Location: India
|
Junior Member |
|
|
Hi, i have a sample table where I am arranging data in required format using 'decode' by hardcoding all values, please tell me how can we do same using pivot method, can pivot be only used when we want aggregate values in oracle?
Also can't we do what i did, using generic max(decode) with select query, if it were a large table (i don't know which would be pivot column here?) ? Thanks for help..
Table is
SQL> CREATE TABLE NOWT
2 (COURSE VARCHAR2(20),MEMBER VARCHAR2(20));
SQL> SELECT * FROM NOWT;
COURSE MEMBER
-------------------- ------------------
ENGLISH JACK
ENGLISH JOHN
ENGLISH MARY
MATHS ROBERT
MATHS DIANA
Let's say required format is :-
COURSE TeamMe TEAMM TEAM
-------------------- ------ ----- ----
ENGLISH Jack JOHN MARY
MATHS Robert DIANA
I can do above by hardcoding values as in
select COURSE,
DECODE(COURSE,'MATHS','Robert','ENGLISH', 'Jack')"TeamMember1",
DECODE(COURSE,'MATHS', 'DIANA','ENGLISH', 'JOHN')"TEAMMEMBER2",
DECODE(COURSE,'MATHS', null ,'ENGLISH', 'MARY')"TEAMMEMBER3" FROM NOWT GROUP BY COURSE;
how can we do that using pivot in Oracle?
|
|
|
Re: pivot query [message #545024 is a reply to message #545023] |
Sun, 26 February 2012 18:33 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You can use row_number to number the rows for each member within each course ordered by whatever you like. You can then use max(decode... in Oracle 10g or pivot in Oracle 11g. Please see the demonstration below.
-- test table and test data:
SCOTT@orcl_11gR2> CREATE TABLE nowt
2 (course VARCHAR2 (11),
3 member VARCHAR2 (11))
4 /
Table created.
SCOTT@orcl_11gR2> INSERT ALL
2 INTO nowt VALUES ('ENGLISH', 'JACK')
3 INTO nowt VALUES ('ENGLISH', 'JOHN')
4 INTO nowt VALUES ('ENGLISH', 'MARY')
5 INTO nowt VALUES ('MATHEMATICS', 'ROBERT')
6 INTO nowt VALUES ('MATHEMATICS', 'DIANA')
7 SELECT * FROM DUAL
8 /
5 rows created.
SCOTT@orcl_11gR2> SELECT * FROM nowt
2 /
COURSE MEMBER
----------- -----------
ENGLISH JACK
ENGLISH JOHN
ENGLISH MARY
MATHEMATICS ROBERT
MATHEMATICS DIANA
5 rows selected.
-- query to add row numbers for members in each course:
SCOTT@orcl_11gR2> SELECT course, member,
2 ROW_NUMBER () OVER
3 (PARTITION BY course
4 ORDER BY ROWID) rn
5 FROM nowt
6 /
COURSE MEMBER RN
----------- ----------- ----------
ENGLISH JACK 1
ENGLISH JOHN 2
ENGLISH MARY 3
MATHEMATICS ROBERT 1
MATHEMATICS DIANA 2
5 rows selected.
-- 10g pivot using query with row numbers:
SCOTT@orcl_11gR2> SELECT course,
2 MAX (DECODE (rn, 1, member)) "TeamMember1",
3 MAX (DECODE (rn, 2, member)) "TeamMember2",
4 MAX (DECODE (rn, 3, member)) "TeamMember3"
5 FROM (SELECT course, member,
6 ROW_NUMBER () OVER
7 (PARTITION BY course
8 ORDER BY ROWID) rn
9 FROM nowt)
10 GROUP BY course
11 /
COURSE TeamMember1 TeamMember2 TeamMember3
----------- ----------- ----------- -----------
ENGLISH JACK JOHN MARY
MATHEMATICS ROBERT DIANA
2 rows selected.
-- 11g pivot using query with row numbers:
SCOTT@orcl_11gR2> SELECT *
2 FROM (SELECT course, member,
3 ROW_NUMBER () OVER
4 (PARTITION BY course
5 ORDER BY ROWID) rn
6 FROM nowt)
7 PIVOT (MAX (member) FOR rn IN
8 (1 AS "TeamMember1",
9 2 AS "TeamMember2",
10 3 AS "TeamMember3"))
11 /
COURSE TeamMember1 TeamMember2 TeamMember3
----------- ----------- ----------- -----------
ENGLISH JACK JOHN MARY
MATHEMATICS ROBERT DIANA
2 rows selected.
|
|
|
Re: pivot query [message #545039 is a reply to message #545023] |
Sun, 26 February 2012 23:27 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also always post your Oracle version, with 4 decimals.
Regards
Michel
[Updated on: Sun, 26 February 2012 23:27] Report message to a moderator
|
|
|
Re: pivot query [message #545178 is a reply to message #545039] |
Mon, 27 February 2012 17:00 |
|
samri
Messages: 18 Registered: January 2012 Location: India
|
Junior Member |
|
|
Hi, Thanks a lot Barbara for explaining, can u also explain why we used max(member) in 11g pivot answer, is it because we needed to use an aggregate function, and if it so then still what would max(member) signify? also, i wanted to confirm this:- while using decode or max(decode) method, i think we shouldn't bother abt column to be pivoted(pivot in its exact meaning as in which column is exactly being pivoted), we should use decode as per the result we want..i sincerely hope u understand what i asked here (sorry if bad english). Also in above examples instead of 'order by rowid' if i use 'order by rownum' i get the same result, would that also be correct way of doing? Again, thanks so much answering & making me understand this.
|
|
|
|
Re: pivot query [message #545183 is a reply to message #545178] |
Mon, 27 February 2012 17:42 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
samri wrote on Mon, 27 February 2012 15:00
...can u also explain why we used max(member) in 11g pivot answer, is it because we needed to use an aggregate function,
Yes, it is because we need to use an aggregate function. Notice the error message that you get if you try to use 11g pivot without an aggregate function:
SCOTT@orcl_11gR2> SELECT *
2 FROM (SELECT course, member,
3 ROW_NUMBER () OVER
4 (PARTITION BY course
5 ORDER BY ROWID) rn
6 FROM nowt)
7 PIVOT (member FOR rn IN
8 (1 AS "TeamMember1",
9 2 AS "TeamMember2",
10 3 AS "TeamMember3"))
11 /
PIVOT (member FOR rn IN
*
ERROR at line 7:
ORA-56902: expect aggregate function inside pivot operation
samri wrote on Mon, 27 February 2012 15:00
and if it so then still what would max(member) signify?
The max and group by course are to group it by course. It is easier to see the difference by observing the results of the 10g method without max and group by. Notice that you get multiple lines. Using max and group by just merges them into one line per course. You could us max or min as it does not matter which. With numeric values, you could also use sum or avg.
SCOTT@orcl_11gR2> SELECT course,
2 DECODE (rn, 1, member) "TeamMember1",
3 DECODE (rn, 2, member) "TeamMember2",
4 DECODE (rn, 3, member) "TeamMember3"
5 FROM (SELECT course, member,
6 ROW_NUMBER () OVER
7 (PARTITION BY course
8 ORDER BY ROWID) rn
9 FROM nowt)
10 /
COURSE TeamMember1 TeamMember2 TeamMember3
----------- ----------- ----------- -----------
ENGLISH JACK
ENGLISH JOHN
ENGLISH MARY
MATHEMATICS ROBERT
MATHEMATICS DIANA
5 rows selected.
samri wrote on Mon, 27 February 2012 15:00
also, i wanted to confirm this:- while using decode or max(decode) method, i think we shouldn't bother abt column to be pivoted(pivot in its exact meaning as in which column is exactly being pivoted), we should use decode as per the result we want..i sincerely hope u understand what i asked here :( (sorry if bad english).
Sorry, but I don't understand the question. If you are asking about a dynamic pivot, then you can use PL/SQL to generate the SELECT statement and execute it.
samri wrote on Mon, 27 February 2012 15:00
Also in above examples instead of 'order by rowid' if i use 'order by rownum' i get the same result, would that also be correct way of doing?...
Maybe. Maybe not. The rownum will give an unpredictable order. You could order it by rowid or rownum or any other column in the table. If you order by member, then you will get it alphabetically by member. If you had some other column in the table, such as a numeric or date value, then you could order by that.
|
|
|
Re: pivot query [message #545280 is a reply to message #545183] |
Tue, 28 February 2012 04:17 |
|
samri
Messages: 18 Registered: January 2012 Location: India
|
Junior Member |
|
|
Quote:The max and group by course are to group it by course. It is easier to see the difference by observing the results of the 10g method without max and group by. Notice that you get multiple lines. Using max and group by just merges them into one line per course. You could us max or min as it does not matter which. With numeric values, you could also use sum or avg.
Thanks, ya i knew there would be multiple lines if we don't use max in 10 g method, but how would that be in 11g method,that was my concern..okay thanks..
Also, what i wanted to ask was, one of our seniors told us that while converting rows to colums or vice-versa using decode function ie,(Decode[expr],[search],[result]) , we should use the column which is being pivoted in place of 'expr' always. However i dont think it is correct, since in my example here too, we are coverting member column's values into rows, & we needed to number the rows per course & had to use that 'rn' column in place of expr. So, I just wanted to confirm that using 10g decode method, it is not necessary that we need to use column to be pivoted as the expression to be matched, it just depends upon what our result set is.
Thanks Barbara for answering & explaining.
|
|
|
Goto Forum:
Current Time: Thu Mar 28 17:50:01 CDT 2024
|