• Pivot and Unpivot are the relational operator in SQL Server.
• They are used to convert table expressions into another table.
• They can be used when we need to convert rows into columns and vice versa.

Pivot : 

• It is used when we convert rows into columns.
• Its result can be acheived by rotating the unique values from one column into the multiple columns as output.

Syntax : 

Select [Non-Pivoted-Column],[Multiple-Pivoted-Columns]
from
(Select [Non-Pivoted-Column]) AS alias-name
PIVOT
(
  (aggregate-function)[Column-Name]
   FOR (column names that you wants to be header same as [Multiple-Pivoted-Columns])
   IN ([Multiple-Pivoted-Columns])
) AS alias-name

• Non-Pivoted-Column is the one column from the table that needs to be a part of header to relate other data.
• Multiple-Pivot-Columns are the distinct values of a column which will be also a part of header i.e. column name.

Let us consider an example of students with marks obtained in different subjects.

Step 1 : 

Create the table Student and insert the data into the table as follows:

Step 2 :

We have three subjects Maths, Physics and Chemistry as distinct values of column [Subject] and we have to relate these subjects with Students and display their marks accordingly so taken the Name in the select query as Non-Pivoted column.

Select Name,                   --Non-Pivoted-COlumn
 Maths, Physics, Chemistry     --Multiple-Pivoted-Columns
from 
(Select Name, Subject, Marks from Student) as table1     --Select records from table
PIVOT
(
  SUM(Marks) for Subject IN (Maths, Physics, Chemistry)  --Perform the sum of marks for the 3 subjects.
) AS table2
Order By table2.Name

It will produce following output:

The above example will help us in converting when we know the value in the column to display as column names.

For unknown value of the columns we can use Dynamic Query.

UnPivot :

• It is used when we have to convert columns into rows.
• It is the exact opposite process of pivot but it will not produce the same output always as expected when the two or more rows are merged as one while pivot.

Let us consider the above example in which we have convert the rows into columns. Now we will store the pivoted result in the temporary table called #tmp and try to convert its columns into rows by unpivot.

Select Name, Maths, Physics, Chemistry into #tmp
from 
(Select Name, Subject, Marks from Student) as table1   
PIVOT
(
  SUM(Marks) for Subject IN (Maths, Physics, Chemistry) 
) AS table2
Order By table2.Name


Select Name, Subject, Marks from #tmp
UNPIVOT
( Marks
   FOR Subject IN (Maths, Physics, Chemistry)
) as table3

It will produce following output:

#Happy coding...

• Please give your suggestion if you find anything incorrect. contact us at team@bitsolve.in