Not getting expected results for pivot query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Not getting expected results for pivot query

Author

Topic

sandeepbhutani304
Starting Member

1 Post

Posted - 2009-03-10 : 05:21:04

I have a table having data:
select * from rota
empid workinghrs onDate
---------- ----------- -----------------------
1 10 2009-01-01 00:00:00.000
2 20 2009-01-01 00:00:00.000
3 10 2009-01-01 00:00:00.000
1 20 2009-01-02 00:00:00.000
2 15 2009-01-02 00:00:00.000
3 12 2009-01-02 00:00:00.000

(6 row(s) affected)

I am running following query:
select empid, [1],[2] from (select empid, workingHrs, onDate, month(ondate) as d from rota) a
pivot
(
sum(workingHrs)
for d
in ([1], [2])
) tab_b

and getting following result:
empid 1 2
---------- ----------- -----------
1 10 NULL
2 20 NULL
3 10 NULL
1 20 NULL
2 15 NULL
3 12 NULL

(6 row(s) affected)

Instead I was expecting
10+20=30 for 1
20+15=35 for 2
and 10+12=22 for 3

Where I am wrong. I want total hours worked by an emp month-wise

Sandeep

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 05:26:03

does this work?


select empid, [1],[2] from 
(select empid, workingHrs, onDate, datepart(mm,onDate) as d from rota) a
pivot
(
sum(workingHrs) 
for d
in ([1], [2])
) tab_b

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-10 : 05:30:03

try this once,

declare @temp table (empid int, workinghrs int, onDate varchar(32))
insert into @temp select 1, 10 ,'2009-01-01'
insert into @temp select 2, 20, '2009-01-01'
insert into @temp select 3, 10, '2009-01-01'
insert into @temp select 1, 20, '2009-01-02'
insert into @temp select 2, 15, '2009-01-02'
insert into @temp select 3, 12,'2009-01-02'

select empid, [1],[2] from (select empid,workinghrs,month(ondate) d from @temp) a
pivot
(
sum(workingHrs)
for d
in ([1], [2])
) tab_b

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-10 : 05:30:46

try this
select sum(case when empid = 1 then workinghrs end) as [1], sum(case when empid = 2 then workinghrs end) as [2],
sum(case when empid = 3 then workinghrs end) as [3]
from @tab

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 05:33:03

quote:
Originally posted by bklr

try this
select sum(case when empid = 1 then workinghrs end) as [1], sum(case when empid = 2 then workinghrs end) as [2],
sum(case when empid = 3 then workinghrs end) as [3]
from @tab

see OP's sample output. the query should be


select empid,
sum(case when month(onDate)= 1 then workinghrs end) as [1], 
sum(case when month(onDate)= 2 then workinghrs end) as [2],
sum(case when month(onDate)= 3 then workinghrs end) as [3]
from @tab
group by empid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources