Home » SQL & PL/SQL » SQL & PL/SQL » pivot query
pivot query [message #545023] Sun, 26 February 2012 17:53 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Sad (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 #545179 is a reply to message #545039] Mon, 27 February 2012 17:02 Go to previous messageGo to next message
samri
Messages: 18
Registered: January 2012
Location: India
Junior Member
Hi Michel, thanks, apologies, will definitely keep it that in mind next time..
Re: pivot query [message #545183 is a reply to message #545178] Mon, 27 February 2012 17:42 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: How to convert single row into multiple rows
Next Topic: Horizontal Fragmentation in Oracle 10g Express edition
Goto Forum:
  


Current Time: Thu Mar 28 17:50:01 CDT 2024