How to Unpivot a table in sql server?

Want to learn how to unpivot a table in SQL Server? If you have learned about PIVOT, its time to learn UNPIVOT too. Here is a the code to demonstrate the use of UNPIVOT on SQL tables.

Want to learn how to UNPIVOT a table in sql server? Sometime situations come where we want to convert a row into multiple columns. In such situations we use UNPIVOT on the data table.

The pivot and unpivot syntax very simple and similar to that of sub queries in sql server 2005-08. However, there is just one simple rule one should keep in mind that while using UNPIVOT or PIVOT we must keep the field length in the UNPIVOT statement and the sub query same.

Table Schema and Insert statements for insertion of data in SQL Server

First we would create a table with the following attributes:

create table Salary(ID int identity,EmpID int,SYear Int,SMonth int,EmpSalary decimal(24,2))

Now insert data in the SQL table using the insert statement as follows:

Insert into Salary values(101,2012,1,12500),(101,2012,2,12000),(101,2012,3,150000)
Insert into Salary values(102,2012,1,22500),(102,2012,2,25000),(102,2012,3,25000)
Insert into Salary values(101,2011,1,12500),(101,2011,2,12000),(101,2011,3,150000)
Insert into Salary values(102,2011,1,22500),(102,2011,2,25000),(102,2011,3,25000)

SQL syntax for UNPIVOT

The sytax for the UNPIVOT statement is:

select ColumnIdName,ColumnFieIdValue,ColumnFieIdCode from
select All column Name With Same length from TableName
) as tableNameofMainTable
(ColumnFieIdValue for ColumnFieIdCode in(columnNameThat Define in subQuery )) as AnyTableofTable

Example to show the use of UNPIVOT using SQL Server

select EmpID,FieIdValue,FieldCode from
select cast(EmpID as Varchar(250)) as EmpID,
CAST(Syear as varchar(250)) as Syear,
CAST(Smonth as varchar(250)) as Smonth,
CAST(EmpSalary as varchar(250)) as EmpSalary
from Salary
) as Un
(FieIdValue for FieldCode in(Syear,Smonth,EmpSalary)) as UnpivotTable


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: