RSS Feed

TRANSPOSE COLUMNS TO ROWS

Before SQL Server 2000, it was very difficult for developers to convert or transpose the column based values into row based values & to convert or transpose the row based values into column based values. But SQL Server 2005 & onwards made this possible.

Below is an example which will transpose values from one or more columns to rows in SQL Server using UNPIVOT operator.

Transpose is required when we have to generate:
1. A single column with n rows from n columns.
2. n columns from single column with n rows.

In this post we will cover first scenario.

A simple example of transposing columns to rows:

We have a table named Salary with 3 columns i.e. EmpName, BasicSal, and NetSal.

SELECT * FROM Salary
EmpNameBasicSalNetSal
Hitesh1500025000
Amit1200021000
Kavita1000015000
Sridhar1500025000
Kartik1700028000
Nimesh2000030000
Kishore1300022000
Namit1700028000

Transpose columns into rows

SELECT EmpName,SalType,Sal
FROM
(SELECT EmpName,BasicSal,NetSal
FROM Salary) S
UNPIVOT
(Sal FOR SalType IN (BasicSal,NetSal))
AS UNPVT;

EmpNameSalTypeSal
HiteshBasicSal15000
HiteshNetSal25000
AmitBasicSal12000
AmitNetSal21000
KavitaBasicSal10000
KavitaNetSal15000
SridharBasicSal15000
SridharNetSal25000
KartikBasicSal17000
KartikNetSal28000
NimeshBasicSal20000
NimeshNetSal30000
KishoreBasicSal13000
KishoreNetSal22000
NamitBasicSal17000
NamitNetSal28000