Menu

Wednesday 16 January 2013

How to Pivot and Unpivot Tables (rows to columns and column to rows)


CREATE TABLE STUDENT_MARKS(
Student_ID INT,
Student_Name VARCHAR(10),
Subject VARCHAR(10),
Marks INT)

INSERT INTO STUDENT_MARKS VALUES
(1,'A','English',75),(1,'A','Maths',88),(1,'A','Science',67),
(2,'B','English',88),(2,'B','Maths',82),(2,'B','Science',55),
(3,'C','English',65),(3,'C','Maths',50),(3,'C','Science',75),
(4,'D','English',76),(4,'D','Maths',100),(4,'D','Science',91)

SELECT Student_ID,Student_Name,Subject,Marks
FROM STUDENT_MARKS

Output:
Student_ID  Student_Name Subject    Marks

----------- ------------ ---------- -----------
1           A            English    75
1           A            Maths      88
1           A            Science    67
2           B            English    88

2           B            Maths      82
2           B            Science    55
3           C            English    65
3           C            Maths      50
3           C            Science    75
4           D            English    76
4           D            Maths      100
4           D            Science    91


--Pivot (convert subjects data into columns and show marks for respective subject for the students)
SELECT Student_ID,Student_Name,[English],[Maths],[Science]
FROM (SELECT Student_ID,Student_Name,Subject,Marks
      FROM STUDENT_MARKS)A
PIVOT (MAX(Marks) FOR SUBJECT IN ([English],[Maths],[Science]))AS PVT

Result:
Student_ID  Student_Name English     Maths       Science

----------- ------------ ----------- ----------- -----------
1           A            75          88          67
2           B            88          82          55
3           C            65          50          75

4           D            76          100         91

--Same result can be obtained without using PIVOT as mentioned below
SELECT Student_ID,Student_Name,
MAX(CASE WHEN Subject='English' THEN Marks ELSE 0 END)AS [English],
MAX(CASE WHEN Subject='Maths' THEN Marks ELSE 0 END)AS [Maths],
MAX(CASE WHEN Subject='Science' THEN Marks ELSE 0 END)AS [Science]
FROM STUDENT_MARKS
GROUP BY Student_ID,Student_Name

--Unpivot
--create a table from query shown above
SELECT Student_ID,Student_Name,[English],[Maths],[Science] INTO STUDENT_MARKS_PVT
FROM (SELECT Student_ID,Student_Name,Subject,Marks
   FROM STUDENT_MARKS)A
PIVOT (MAX(Marks) FOR SUBJECT IN ([English],[Maths],[Science]))AS PVT

SELECT Student_ID,Student_Name,[English],[Maths],[Science] FROM STUDENT_MARKS_PVT

Output:
Student_ID  Student_Name English     Maths       Science

----------- ------------ ----------- ----------- -----------
1           A            75          88          67
2           B            88          82          55
3           C            65          50          75

4           D            76          100         91


--Unpivot (convert subjects and marks into separate column for the students)
SELECT Student_ID,Student_Name,Subject,Marks
FROM (SELECT Student_ID,Student_Name,[English],[Maths],[Science]
   FROM STUDENT_MARKS_PVT)A
UNPIVOT (Marks FOR Subject IN ([English],[Maths],[Science]))AS UNPVT

Result:
Student_ID  Student_Name Subject   Marks

----------- ------------ --------- -----------
1           A            English   75
1           A            Maths     88
1           A            Science   67
2           B            English   88

2           B            Maths     82
2           B            Science   55
3           C            English   65
3           C            Maths     50
3           C            Science   75
4           D            English   76
4           D            Maths     100
4           D            Science   91



No comments:

Post a Comment