Pivot and Unpivot in SQL server.

Hi friends,

 Today I want to discuss about most important and easily forgotten topic in SQL.

 

Basically, Pivot unpivot are relational operations used to manipulate the data and convert table expression to another.

 

Pivot is used to transfer/convert row level data to column level data.

Unpivot is reverse of this column level to row level.

 

We will take an example in this to understand in better way.

CREATE TABLE Student 

( 

   Name [nvarchar](max), 

   [Year] [int] , 

   Fees [int] 

)

Here I taken a table named Student with column names Name and Year and Fees.

Insert some data into the table.

INSERT INTO Student 

SELECT 'Pankaj',2010,72500 UNION ALL 

SELECT 'Rahul',2010,60500 UNION ALL 

SELECT 'Sandeep',2010,52000 UNION ALL 

SELECT 'Pankaj',2011,45000 UNION ALL 

SELECT 'Sandeep',2011,82500 UNION ALL 

SELECT 'Rahul',2011,35600 UNION ALL 

SELECT 'Pankaj',2012,32500 UNION ALL 

SELECT 'Pankaj',2010,20500 UNION ALL 

SELECT 'Rahul',2011,200500 UNION ALL 

SELECT 'Sandeep',2010,32000

 

SELECT * FROM Student;

 

So this is the table they have and using to store student data.

Management what to see year wise how much each student paying and they want the data to analyze.

Here in this case we can use Pivot to convert year which is in row level to column level.

Syntax for pivot:

SELECT <non-pivoted column>, 

       <list of pivoted column> 

FROM 

(<SELECT query  to produces the data>) 

    AS <alias name> 

PIVOT 

( 

<aggregation function>(<column name>) 

FOR 

[<column name that  become column headers>] 

    IN ( [list of  pivoted columns]) 

 

) AS <alias name  for  pivot table> 

 

Here comes our query for Pivot:

SELECT Name, [2010],[2011],[2012] FROM  

(SELECT Name, [Year] , Fees FROM Student )Tab1 

PIVOT 

( 

SUM(Fees) FOR [Year] IN ([2010],[2011],[2012])) AS Tab2 

ORDER BY Tab2.Name 

 

Here is the example code to execute and understand the syntax with data

CREATE TABLE #Student 

( 

   Name [nvarchar](max), 

   [Year] [int] , 

   Fees [int] 

)

 

 

INSERT INTO #Student 

SELECT 'Pankaj',2010,72500 UNION ALL 

SELECT 'Rahul',2010,60500 UNION ALL 

SELECT 'Sandeep',2010,52000 UNION ALL 

SELECT 'Pankaj',2011,45000 UNION ALL 

SELECT 'Sandeep',2011,82500 UNION ALL 

SELECT 'Rahul',2011,35600 UNION ALL 

SELECT 'Pankaj',2012,32500 UNION ALL 

SELECT 'Pankaj',2010,20500 UNION ALL 

SELECT 'Rahul',2011,200500 UNION ALL 

SELECT 'Sandeep',2010,32000

 

 

SELECT [Year], Pankaj,Rahul,Sandeep into #PivotTable1FROM  

(SELECT Name, [Year] , Fees FROM #Student )Tab1 

PIVOT 

( 

SUM(Fees) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2 

ORDER BY Tab2.Fees 

 

 

SELECT Names,[Year],Fees FROM #PivotTable t 

UNPIVOT 

( 

Fees FOR Names IN ([Pankaj],[Rahul],[Sandeep]) 

) AS TAb2

 

Thanks for supporting. Please follow and share to friends. If any type of doubts feel free to ask.