Solved

Displaying results on one line in query results

Posted on 2008-06-17
10
248 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Help - MSSQL - Averages 5 30
Merge two rows in SQL 4 25
MS SQL Server time between records 14 46
SQL trigger 5 21
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

809 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