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
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
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,
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
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
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