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
UNPIVOT
(ColumnFieIdValue for ColumnFieIdCode in(columnNameThat Define in subQuery )) as AnyTableofTableExample 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
unpivot
(FieIdValue for FieldCode in(Syear,Smonth,EmpSalary)) as UnpivotTable