pivot and unpivot in sql server

 ---PIVOT EXAMPLE 

Drop table Students;

CREATE TABLE Students

(

Id INT PRIMARY KEY IDENTITY,

StudentName VARCHAR (50),

Course VARCHAR (50),

Score INT

);

INSERT INTO dbo.Students(StudentName,Course,Score) VALUES ('Abhishek', 'English', 95 )

INSERT INTO Students VALUES ('Abhishek', 'History', 82)

INSERT INTO Students VALUES ('Nayan', 'English', 45)

INSERT INTO Students VALUES ('Nayan', 'History', 78);

SELECT * FROM  

(SELECT StudentName,Score,Course FROM Students) AS StudentTable

PIVOT( SUM(Score) FOR Course IN ([English],[History])) AS SchoolPivot 

--UNPIVOT 

Drop table Students;

CREATE TABLE Students

(

Id INT PRIMARY KEY IDENTITY,

StudentName VARCHAR (50),

Math INT,

English INT,

History INT,

Science INT

);

INSERT INTO Students VALUES ('Abhishek', 78, 85, 91, 76 )

INSERT INTO Students VALUES ('Nayan', 87, 90, 82, 87)

SELECT StudentName, Course, Score

FROM Students

UNPIVOT

( Score FOR Course in (Math, English, History, Science)) AS SchoolUnpivot

--now pivot the above 

select * from (

SELECT StudentName, Course, Score

FROM Students

UNPIVOT

( Score FOR Course in (Math, English, History, Science)) AS SchoolUnpivot) AS StudentTable

PIVOT( SUM(Score) FOR Course IN ([English],[History])) AS SchoolPivot