PIVOT AND UN-PIVOT - SQL
In several scenarios we would have to change the rows to columns and vice versa in a our select query to overcome some bad database designs.Unpivot and pivot does exactly that. All though SSIS provides you with the feature to do unpivot and pivot , it is highly advisable to do it in sql query , unless you have the data coming from a non – oledb source or from two different sources.
UNPIVOT
Unpivot rotates the columns of the table to the column values.
Following shows a simple example
Term
|
ENGLISH
|
MATHS
|
SCIENCE
|
FRENCH
|
MidTerm1
|
78
|
86
|
76
|
78
|
MidTerm2
|
77
|
44
|
76
|
56
|
MidTerm3
|
98
|
47
|
87
|
76
|
MidTerm4
|
35
|
89
|
77
|
69
|
SELECT Term ,
Subject,
Marks
FROM (SELECT Term,ENGLISH,MATHS,SCIENCE,FRENCH
FROM Scores WHERE STUDENT_ID = 345950) p
UNPIVOT
(Marks
FOR Subject IN ([ENGLISH],[MATHS],[SCIENCE],[FRENCH] ) ) AS unpvt
ORDERBY Term,
Subject;
GO
