Converting the row data into column data in Oracle

by Bharathlenin
(Chenai)

In my Oracle database I have a table of student results with data like this,


Name Marks Subject
_______________________

Hari 25 ENG
Banu 30 MAT
Sam 80 SCI
Hari 85 MAT
Hari 20 SCI



**
I want to convert it like

Name ENG MAT SCI
_______________________

Hari 25 85 20
Banu 30 56 12


That's a really good question and one that comes up often and there are several ways to do this is to use an SQL CASE statement. The CASE statement in Oracle SQL works just the same as it does in C and other programming languages in that each possibility is evaluated to determine which one is to be taken.

Before we dive into the answer though let's look at the approach we take to arrive there. As always we want to take a step by step approach to ensure that we avoid mistakes.

We'll start with creating our table and populating it with our test data.

CREATE TABLE STUDENTS
(
student_id NUMBER,
student_name VARCHAR2(30),
subject VARCHAR2(30),
mark NUMBER
);


Having created our table we can now populate it with the following SQL statements:-

INSERT INTO students
(student_id , student_name , mark , subject )
VALUES
(1,'Hari' , 20 , 'SCI');

INSERT INTO students
(student_id , student_name , mark , subject )
VALUES
(1,'Hari' , 85 , 'MAT');

INSERT INTO students
(student_id , student_name , mark , subject )
VALUES
(1,'Hari', 25, 'ENG');

INSERT INTO students
(student_id , student_name , mark , subject )
VALUES
(2,'Banu' , 30 , 'MAT');

INSERT INTO students
(student_id , student_name , mark , subject )
VALUES
(3,'Sam' , 80 , 'SCI');


With our test data created, the first thing we want to do is to have the subject names as column headers which we can achieve by using a CASE statement as demonstrated in the following SQL:


SELECT
 student_name Name
,CASE subject WHEN 'ENG' THEN mark ELSE 0 END Eng
,CASE subject WHEN 'MAT' THEN mark ELSE 0 END Mat
,CASE subject WHEN 'SCI' THEN mark ELSE 0 END Sci
FROM students
ORDER BY student_name;

This is the output of that query:

NAME ENG MAT SCI
Banu   0  30   0
Hari  25   0   0
Hari   0   0  20
Hari   0  85   0
Sam    0   0  80

That query takes us part way to our goal - the subject name is the column header and each mark appears under the correct subject. It has one major flaw however - there is till more than one row for each student, and if there were more than one row per subject the output would have one row for each mark for each subject for each student. We want just one row per student so we need to refine the query by summing all the students marks by subject.

The next version of our Oracle SQL ststement looks like this:

SELECT
 student_name Name
,SUM(CASE subject WHEN 'ENG' THEN mark ELSE 0 END) Eng
,SUM(CASE subject WHEN 'MAT' THEN mark ELSE 0 END) Mat
,SUM(CASE subject WHEN 'SCI' THEN mark ELSE 0 END) Sci
FROM students
GROUP BY student_name;

The results of that query give us exactly what we want as shown by the following output.


NAME ENG MAT SCI
Banu   0  30   0
Sam    0   0  80
Hari  25  85  20

As we mentioned earlier there are other ways of doing this but this is the simplest.

For more help see our series of Oracle SQL tutorials and for information about self study courses and formal training see the Oracle training page.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.