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

EmpName	BasicSal	NetSal
Hitesh	15000	25000
Amit	12000	21000
Kavita	10000	15000
Sridhar	15000	25000
Kartik	17000	28000
Nimesh	20000	30000
Kishore	13000	22000
Namit	17000	28000

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;

EmpName	SalType	Sal
Hitesh	BasicSal	15000
Hitesh	NetSal	25000
Amit	BasicSal	12000
Amit	NetSal	21000
Kavita	BasicSal	10000
Kavita	NetSal	15000
Sridhar	BasicSal	15000
Sridhar	NetSal	25000
Kartik	BasicSal	17000
Kartik	NetSal	28000
Nimesh	BasicSal	20000
Nimesh	NetSal	30000
Kishore	BasicSal	13000
Kishore	NetSal	22000
Namit	BasicSal	17000
Namit	NetSal	28000