How to Use PIVOT Table in SQL Server

This blog demonstrate how to use PIVOT table in sql server. So let's start with a scenario. In this case we have lots of employee who their basic information, for this we have a simple table that looks like this. Create table tblGenderEMP(EmpID Int, EmpName varchar(100), Gender char(1), amt Int)
 
Just add empname, gender in table. So let's fill it with some data.

insert into tblGenderEMP values (1, 'mohan', 'M' ,52)

insert into tblGenderEMP values (2, 'mohini', 'F',65)

insert into tblGenderEMP values (3, 'suraj', 'M',500)

insert into tblGenderEMP values (4, 'surya', 'M',600)

insert into tblGenderEMP values (5, 'ragini', 'F',70)

insert into tblGenderEMP values (6, 'rohini', 'F',250)

insert into tblGenderEMP values (7, 'raju', 'M',320)

Now if we select then display like:

select * from tblGenderEMP

Result

Empid Empname Gender AMT
1 Mohan M 52
2 Mohini F 65
3 Suraj M 500
4 Surya M 600
5 Ragini F 70
6 Rohini F 250
7 Raju M 320

To find out how may amount contribute in Male and How many amount contribute Female:

M F
1472 385

Your Quey become as:

SELECT [M], [F]

FROM

(

SELECT sp.gender,sp.amt

FROM tblGenderEMP as sp

) p

PIVOT

(

SUM (amt)

FOR gender

IN ([M], [F])

) AS pvt

Ebook Download
View all
Learn
View all