Hello Friends,
Have you ever been in a situation where you as a developer knows that your data is stored in your table rows, but you would like to present the data as a column ??

There are many way to handle this situation. Here I am sharing few of them which I know.​

Below is the sample table and data :
————————————————————

CREATE TABLE yr_table (
Roll_No NUMBER,
SubjectVARCHAR2(20),
Marks NUMBER
);

insert into yr_table VALUES (1212324,'MTH',90);
insert into yr_table VALUES (1212324,'PHY',72);
insert into yr_table VALUES (1212324,'CHE',85);
insert into yr_table VALUES (1212324,'BIO',78);
insert into yr_table VALUES (1212334,'MTH',85);
insert into yr_table VALUES (1212334,'PHY',65);
insert into yr_table VALUES (1212334,'CHE',74);
insert into yr_table VALUES (1212672,'MTH',88);
insert into yr_table VALUES (1212672,'PHY',42);
insert into yr_table VALUES (1212672,'BIO',12);
COMMIT;

——————————————————————-

Now I would like to represent above data into below format :

We can achieve above output  in different way.

# Method 1 ->  Using DECODE  function

SELECT ROLL_NO,
MAX( DECODE( SUBJECT , 'MTH' , MARKS ) ) AS MTH ,
MAX( DECODE( SUBJECT , 'PHY' , MARKS ) ) AS PHY ,
MAX( DECODE( SUBJECT , 'CHE' , MARKS ) ) AS CHE ,
MAX( DECODE( SUBJECT , 'BIO' ,  MARKS ) ) AS BIO
FROM yr_table
GROUP BY ROLL_NO ORDER BY 1 ;​

To understand the above query, first see  the below output of above query without using MAX function then you can understand how above query works.

# Method 2 -> Using CASE​ statement 

SELECT ROLL_NO,
MAX( CASE WHEN SUBJECT = 'MTH' THEN MARKS END) MTH ,
MAX( CASE WHEN SUBJECT = 'PHY' THEN MARKS END) PHY ,
MAX( CASE WHEN SUBJECT = 'CHE' THEN MARKS END) CHE ,
MAX( CASE WHEN SUBJECT = 'BIO' THEN MARKS END) BIO
FROM yr_table
GROUP BY ROLL_NO ORDER BY 1 ;​

Here CASE statement works same as DECODE function.

# Method 3 -> Using PIVOT Operator​

The PIVOT and the UNPIVOT operators were introduced in Oracle version 11g. The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. The following query will give the same result as the query above, just by using the PIVOT operator.

SELECT * FROM yr_table
PIVOT (
MAX ( MARKS )  FOR (SUBJECT) IN ('MTH' AS MTH, 'PHY' AS PHY, 'CHE' AS CHE, 'BIO' AS BIO)
)
ORDER BY 1​​

You can check below link for more clarification on PIVOT Operator.  http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html 

# Method 4 -> Using WITH clause and PIVOT Operator​   ​

WITH TMP AS (
SELECT ROLL_NO,SUBJECT , MARKS FROM yr_table
)
SELECT * FROM TMP
PIVOT (
MAX(MARKS) FOR (SUBJECT) IN ('MTH' AS MTH, 'PHY' AS PHY, 'CHE' AS CHE, 'BIO' AS BIO)
)
ORDER BY 1

# Method 5 -> Using WITH clause and Sub-query

WITH TMP AS (
 SELECT ROLL_NO,SUBJECT , MARKS FROM yr_table
)
SELECT Y.ROLL_NO ,
( SELECT TMP.MARKS FROM  TMP WHERE TMP.ROLL_NO = Y.ROLL_NO AND TMP.SUBJECT = 'MTH') AS MTH ,
( SELECT TMP.MARKS FROM  TMP WHERE TMP.ROLL_NO = Y.ROLL_NO AND TMP.SUBJECT = 'PHY') AS PHY ,
( SELECT TMP.MARKS FROM  TMP WHERE TMP.ROLL_NO = Y.ROLL_NO AND TMP.SUBJECT = 'CHE') AS CHE ,
( SELECT TMP.MARKS FROM TMP WHERE  TMP.ROLL_NO = Y.ROLL_NO AND TMP.SUBJECT = 'BIO') AS BIO
FROM (SELECT DISTINCT ROLL_NO FROM yr_table ) Y
ORDER BY 1;

# Method 6 -> Using Multiple Joins

SELECT DISTINCT
 A.ROLL_NO ,
 B.MARKS AS MTH ,
 C.MARKS AS PHY ,
 D.MARKS AS CHE ,
 E.MARKS AS BIO
FROM yr_table A
LEFT JOIN yr_table B
ON A.ROLL_NO = B.ROLL_NO AND B.SUBJECT = 'MTH'
LEFT JOIN yr_table C
ON A.ROLL_NO = C.ROLL_NO AND C.SUBJECT = 'PHY'
LEFT JOIN yr_table D
ON A.ROLL_NO = D.ROLL_NO AND D.SUBJECT = 'CHE'
LEFT JOIN yr_table E
ON A.ROLL_NO = E.ROLL_NO AND E.SUBJECT = 'BIO'
ORDER BY 1;

Friends, please share your knowledge as well, if you know any other method.

Follow me at :
Facebook