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:
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