Solved

Displaying results on one line in query results

Posted on 2008-06-17
10
245 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now