CODEWITHSUNDEEP
sqloracle-databaseoracle11gpivot-table
Andrew Watson
Andrew Watson

Reputation: 15

Transpose Entire Table in Oracle 11gR2 (using Pivot)

I am currently using Oracle 11gR2 and an attempting to transpose an entire table using Pivot but I am having some issues doing so.

Student Table:

Student_id, math_grade, english_grade,  history_grade,  science_grade
1           A           B               C+               C-
2           B           B+              A-               B-
3           C           C               C                D
4           A+          A               A-               B+

Desired Outcome:

Student_id      1       2       3       4   
Math_grade      A       B       C       A+
English_grade   B       B+      C       A
History_grade   C+      A-      C       A-
Science_grade   C-      B-      D       B+

Query:

Select * 
From (
Select student_id, math_grade, english_grade, history_grade, science_grade
From student_table
Where student_id in (1,2,3,4); )
Pivot ( min(math_grade) as mathgrade, min(english_grade) as englishgrade, min(history_grade) as historygrade, min(science_grade) as sciencegrade
For student_id in (1 as Student1, 2 as Student2, 3 as Student3, 4 as student4) );

Output:

I am somehow transposing all the data into one long row

Student1_mathgrade, student1_englishgrade,……student4_historygrade, student4_sciencegrade
A                   B                       A-                     B+

Any advice on how to get the desired outcome?

Upvotes: 1

Views: 311

Answers (2)

Bob
Bob

Reputation: 1055

This is basically the same as the answer provided by @WalterM, but instead of unions, I use UNPIVOT.

select * from student_table
unpivot(grade for subject in (math_grade, english_grade, history_grade, science_grade))
pivot(min(grade) for student_id in (1, 2, 3, 4));

Upvotes: 3

WalterM
WalterM

Reputation: 36

Since the PIVOT clause is usually used to transpose aggregate data, it helps to convert your columns into rows, before using PIVOT. You can try the following:

SELECT *
  FROM (
   SELECT student_id, 'MATH' "CLASS", math_grade "GRADE"
     FROM test
   UNION
   SELECT student_id, 'ENGLISH' "CLASS", english_grade "GRADE"
     FROM test
   UNION
   SELECT student_id, 'HISTORY' "CLASS", history_grade "GRADE"
     FROM test
   UNION
   SELECT student_id, 'SCIENCE' "CLASS", science_grade "GRADE"
     FROM test
  )
  PIVOT (
    MIN(grade)
    FOR student_id IN
    (1 student_1, 2 student_2, 3 student_3, 4 student_4)
  );

You may need to modify the column names and the row order, but that can be done with a wrapper SELECT statement.

Upvotes: 0

Related Questions