Transforming rows to columns (PIVOT/CROSS TAB) and columns to rows is (UNPIVOT). Reverse operation of PIVOT is UNPIVOT.
Actual Table :
StudentName
|
Marks
|
Grade
|
A
|
10
|
I
|
B
|
20
|
II
|
C
|
40
|
IV
|
D
|
30
|
III
|
Select studentname, [I], [II], [III], [IV]
from
( Select grade, studentname, marks from tbl_student) as sourcetable
Pivot ( avg(marks) for grade in ([I],[II],[III],[IV)) as pivotable order by IV desc,III desc,II desc,I desc
from
( Select grade, studentname, marks from tbl_student) as sourcetable
Pivot ( avg(marks) for grade in ([I],[II],[III],[IV)) as pivotable order by IV desc,III desc,II desc,I desc
PIVOT Result Table :
StudentName
|
I
|
II
|
III
|
IV
|
A
|
10
| |||
B
|
20
| |||
C
|
40
| |||
D
|
30
|
Actual Table :
StudentName
|
I
|
II
|
III
|
IV
|
A
|
10
| |||
B
|
20
| |||
C
|
40
| |||
D
|
30
|
select studentname,Marks,Grade
from tbl_stdmarksdata
unpivot
(Marks for Grade in (I,II,III,IV) ) as tblunpvt
UNPIVOT Result Table :
StudentName
|
Marks
|
Grade
|
A
|
10
|
I
|
B
|
20
|
II
|
C
|
40
|
IV
|
D
|
30
|
III
|
SQL:PIVOT AND UN-PIVOT - SQL
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
TERM
|
SUBJECT
|
MARKS
|
MidTerm1
|
ENGLISH
|
78
|
MidTerm1
|
MATHS
|
86
|
MidTerm1
|
SCIENCE
|
76
|
MidTerm1
|
FRENCH
|
78
|
MidTerm2
|
ENGLISH
|
77
|
MidTerm2
|
MATHS
|
44
|
MidTerm2
|
SCIENCE
|
76
|
MidTerm2
|
FRENCH
|
56
|
MidTerm3
|
ENGLISH
|
98
|
MidTerm3
|
MATHS
|
47
|
MidTerm3
|
SCIENCE
|
87
|
MidTerm3
|
FRENCH
|
76
|
MidTerm4
|
ENGLISH
|
35
|
MidTerm4
|
MATHS
|
89
|
MidTerm4
|
SCIENCE
|
77
|
MidTerm4
|
FRENCH
|
69
|
Unpivot on Multiple Columns
Consider the following scenario
Company
|
CEOFName
|
CEOLName
|
ManagerFname
|
ManagerLname
|
ExecutiveFname
|
ExecutiveLname
|
Alpha
|
John
|
Smith
|
Michael
|
Scott
|
Tim
|
Hardy
|
Beta
|
Pamela
|
Lee
|
Thomas
|
Right
|
Jim
|
Watson
|
Now if you require the result set such as
Company
|
ContactFirstName
|
ContactLastName
|
Alpha
|
John
|
Smith
|
Alpha
|
Michael
|
Scott
|
Alpha
|
Tim
|
Hardy
|
Beta
|
Pamela
|
Lee
|
Beta
|
Thomas
|
Right
|
Beta
|
Jim
|
Watson
|
For the desired result you will have to unpivot multiple columns , but that is not the main challenge .It is quite tricky to map the firstname with the respective last name.
Select Company,
FirstName,
LastName
From
(select
Company, CEOFName, CEOLName, ManagerFname, ManagerLname, ExecutiveFname, ExecutiveLname
From Company) main
Unpivot (FirstName for FirstNames in (CEOFName, ManagerFname, ExecutiveFname) fn
Unpivot (LastName for LastNames in (CEOLName, ManagerLname, ExecutiveFname) ln
WhereRight(FirstName,1) = Right(LastName,1)
The where clause “Where Right(FirstName,1) = Right(LastName,1) “ is used for mapping the firstName against the respective lastname.
That is using the number on the end of the column names to ties together the supplier with the city, so firstName1 = lastname1, firstName2 = lastname2
Alternatively you could also use a substring or the following
WHERE ( (FirstNames = ‘CEOFName’
AND LastNames = ' CEOLName ')
OR (FirstNames = ' ManagerFname '
AND LastNames = ' ManagerLname ')
OR (FirstNames = ' ExecutiveFname '
AND LastNames = ' ExecutiveFname'))
PIVOT
Pivot lets you change the unique values in one column in to columns in the result set.
Lets consider the below example again. A sample example is provided for pivoting the below dataset.
TERM
|
SUBJECT
|
MARKS
|
MidTerm1
|
ENGLISH
|
78
|
MidTerm1
|
MATHS
|
86
|
MidTerm1
|
SCIENCE
|
76
|
MidTerm1
|
FRENCH
|
78
|
MidTerm2
|
ENGLISH
|
77
|
MidTerm2
|
MATHS
|
44
|
MidTerm2
|
SCIENCE
|
76
|
MidTerm2
|
FRENCH
|
56
|
MidTerm3
|
ENGLISH
|
98
|
MidTerm3
|
MATHS
|
47
|
MidTerm3
|
SCIENCE
|
87
|
MidTerm3
|
FRENCH
|
76
|
MidTerm4
|
ENGLISH
|
35
|
MidTerm4
|
MATHS
|
89
|
MidTerm4
|
SCIENCE
|
77
|
MidTerm4
|
FRENCH
|
69
|
SELECT Term,[ENGLISH],[ MATHS],[ SCIENCE],[ FRENCH]
FROM (SELECT Term,Subject,MarksFROM Table) AS pvt
PIVOT
(SUM(Marks) – Aggregate is to get unique values for cells
FOR SUBJECT IN ( [ENGLISH],[ MATHS],[ SCIENCE],[ FRENCH] ) ) AS pvo
ORDERBY TERM;