Solved

Displaying results on one line in query results

Posted on 2008-06-17
10
247 Views
Last Modified: 2010-04-21
I am using the query below to return the number of customers that are labeled as A, B, C, D.  When I run my query it puts A on one line, and B on another like below.

32      A      Bill Smith
49      B      Bill Smith
37      C      Bill Smith
188      D      Bill Smith
22      A      Mike Jones
6      B      Mike Jones
41      C      Mike Jones
274      D      Mike Jones

Ideally I would like it to be like
32   49  37  188    Bill Smith
22    6    41  274   Mike Jones

I don't have any idea how to approach this.
select count(*) as Total, hi_customerratingname as Rating,owneridname as SE 
from filteredaccount
join filteredsystemuser
on (filteredaccount.ownerid =filteredsystemuser.systemuserid
and filteredsystemuser.title = 'Sales Engineer')
Where hi_customerratingname in ('a', 'b', 'c', 'd')
and filteredaccount.statuscodename = 'active'
 
group by owneridname, hi_customerratingname 
order by owneridname asc

Open in new window

0
Comment
Question by:stl-it
  • 5
  • 5
10 Comments
 
LVL 19

Expert Comment

by:folderol
ID: 21804755
select
case Rating when 'A' then Total else 0 end as A,
case Rating when 'B' then Total else 0 end as B,
case Rating when 'C' then Total else 0 end as C,
case Rating when 'D' then Total else 0 end as D,
SE
from
(
  select count(*) as Total, hi_customerratingname as Rating,owneridname as SE
  from filteredaccount
  join filteredsystemuser
  on (filteredaccount.ownerid =filteredsystemuser.systemuserid
  and filteredsystemuser.title = 'Sales Engineer')
  Where hi_customerratingname in ('a', 'b', 'c', 'd')
  and filteredaccount.statuscodename = 'active'
  group by owneridname, hi_customerratingname
)
group by SE
order by SE asc
0
 
LVL 19

Expert Comment

by:folderol
ID: 21804791
Wow, not concentrating today :)  This is better....

select
sum(case Rating when 'A' then Total else 0 end) as A,
sum(case Rating when 'B' then Total else 0 end) as B,
sum(case Rating when 'C' then Total else 0 end) as C,
sum(case Rating when 'D' then Total else 0 end) as D,
SE
from
(
  select count(*) as Total, hi_customerratingname as Rating,owneridname as SE
  from filteredaccount
  join filteredsystemuser
  on (filteredaccount.ownerid =filteredsystemuser.systemuserid
  and filteredsystemuser.title = 'Sales Engineer')
  Where hi_customerratingname in ('a', 'b', 'c', 'd')
  and filteredaccount.statuscodename = 'active'
  group by owneridname, hi_customerratingname
)
group by SE
order by SE asc
0
 

Author Comment

by:stl-it
ID: 21805214
That code makes sense but it's giving me a Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'group'.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 19

Accepted Solution

by:
folderol earned 250 total points
ID: 21806021
Had a hard weekend I guess....

select
sum(case tempresult.Rating when 'A' then Total else 0 end) as A,
sum(case tempresult.Rating when 'B' then Total else 0 end) as B,
sum(case tempresult.Rating when 'C' then Total else 0 end) as C,
sum(case tempresult.Rating when 'D' then Total else 0 end) as D,
tempresult.SE
from
(
  select count(*) as Total, hi_customerratingname as Rating,owneridname as SE
  from filteredaccount
  join filteredsystemuser
  on (filteredaccount.ownerid =filteredsystemuser.systemuserid
  and filteredsystemuser.title = 'Sales Engineer')
  Where hi_customerratingname in ('a', 'b', 'c', 'd')
  and filteredaccount.statuscodename = 'active'
  group by owneridname, hi_customerratingname
)
as tempresult
group by tempresult.SE
order by tempresult.SE asc

Forgot the "as" after the closing parenthesis.
All the columns of course do not need the virtual table reference, except for instructional purposes...
0
 

Author Closing Comment

by:stl-it
ID: 31468031
perfect!!!!!  Wow, that taught me a lot. Thanks
0
 

Author Comment

by:stl-it
ID: 21806198
If I were to include accounts that had no rating would I do something like this?
select 
sum(case tempresult.Rating when 'A' then Total else 0 end) as A,
sum(case tempresult.Rating when 'B' then Total else 0 end) as B,
sum(case tempresult.Rating when 'C' then Total else 0 end) as C,
sum(case tempresult.Rating when 'D' then Total else 0 end) as D,
sum(case tempresult.Rating when 'null' then Total else 0 end) as Z,
tempresult.SE
from
(
  select count(*) as Total, hi_customerratingname as Rating,owneridname as SE 
  from filteredaccount
  join filteredsystemuser
  on (filteredaccount.ownerid =filteredsystemuser.systemuserid
  and filteredsystemuser.title = 'Sales Engineer')
  Where filteredaccount.statuscodename = 'active'
  group by owneridname, hi_customerratingname 
)
as tempresult
group by tempresult.SE
order by tempresult.SE asc

Open in new window

0
 
LVL 19

Expert Comment

by:folderol
ID: 21807577
Funny you should ask that, I just answered that exact question on another thread.
No, your code
sum(case tempresult.Rating when 'null' then Total else 0 end) as Z,
will not work.  Change it to
sum(case when tempresult.Rating is null then Total else 0 end) as Z,

Case must have an evaluatable expression.  Mine evaluates true or false.  Your syntax, in english, is
case undefined when undefined then ....
since null is not the same as an empty string, it is undefined.  NULL = NULL is not correct either.  You have to re-write it so it has the form TRUE = TRUE, if you want to compare two columns and you suspect both are null.  Unrelated, but you can do this
select SE = null from tempresult
which will reset SE to null, if you had a reason to do that.  Sometimes that is interesting when using Insert or Update.

0
 

Author Comment

by:stl-it
ID: 21807687
Gotcha.    I used the following and it all works.
select 
sum(case tempresult.Rating when 'A' then Total else 0 end) as A,
sum(case tempresult.Rating when 'B' then Total else 0 end) as B,
sum(case tempresult.Rating when 'C' then Total else 0 end) as C,
sum(case tempresult.Rating when 'D' then Total else 0 end) as D,
sum(case tempresult.Rating when 'Z' then Total else 0 end) as Blank,
tempresult.SE
from
(
  select count(*) as Total, isnull(hi_customerratingname, 'Z')  as Rating,owneridname as SE 
  from filteredaccount
  join filteredsystemuser
  on (filteredaccount.ownerid =filteredsystemuser.systemuserid
  and filteredsystemuser.title = 'Sales Engineer')
  Where filteredaccount.statuscodename = 'active'
  group by owneridname, hi_customerratingname 
)
as tempresult
group by tempresult.SE
order by tempresult.SE asc

Open in new window

0
 
LVL 19

Expert Comment

by:folderol
ID: 21809011
Your approach is the best, always better to eliminate the nulls at the source, sort of like termites!
0
 

Author Comment

by:stl-it
ID: 21809783
thanks again
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question