Help in Query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Help in Query

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-05-24 : 05:45:31

declare @table table
(
cust_id int,
cust_name varchar(10),
action varchar(20),
Mode varchar(15),
date_time datetime
)

insert into @table
select 1, 'john', 'CallOperator', 'Computer', '2006-04-10 10:00' union all
select 1, 'john', 'CallOperator', 'Computer', '2006-04-10 10:01' union all
select 1, 'john', 'CallOperator', 'phone', '2006-04-10 10:05' union all
select 1, 'john', 'CallOperator', 'phone', '2006-04-10 10:15' union all
select 2, 'jothi', 'CallOperator', 'computer', '2006-04-10 20:00' union all
select 2, 'jothi', 'CallOperator', 'phone', '2006-04-10 20:10'

select * from @table

select cust_name,action,mode,
case when (action = 'CallOperator' and Mode ='Computer') then count(mode) end as 'ComputerConnect',
case when (action = 'CallOperator' and Mode ='phone') then count(mode) end as 'PhoneConnect'
from @table
group by action,mode,cust_name
order by cust_name

cust_name action mode ComputerConnect PhoneConnect
---------- -------------------- --------------- --------------- ------------
john CallOperator Computer 2 NULL
john CallOperator phone NULL 2
jothi CallOperator computer 1 NULL
jothi CallOperator phone NULL 1

but i need the output like this
cust_name action mode omputerCo PhoneConnect
---------- -------------------- --------------- --------------- -----john CallOperator Computer 2 2
jothi CallOperator computer 1 1
how can i do this

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-24 : 06:02:31

Like this?

select cust_name, action, 'Computer' as 'not sure why this is here',
    sum(case when mode = 'Computer' then 1 else 0 end) as 'Computer count',
    sum(case when mode = 'phone' then 1 else 0 end) as 'Phone count'
from @table
group by
cust_name, action

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-24 : 06:47:50

or see if you want this type of result
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-05-24 : 10:01:54

Thanks a lot. This Solution will work...

quote:
Originally posted by RyanRandall

Like this?

select cust_name, action, 'Computer' as 'not sure why this is here',
sum(case when mode = 'Computer' then 1 else 0 end) as 'Computer count',
sum(case when mode = 'phone' then 1 else 0 end) as 'Phone count'
from @table
group by
cust_name, action

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources