• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Displaying results on one line in query results

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
stl-it
Asked:
stl-it
  • 5
  • 5
1 Solution
 
folderolCommented:
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
 
folderolCommented:
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
 
stl-itAuthor Commented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
folderolCommented:
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
 
stl-itAuthor Commented:
perfect!!!!!  Wow, that taught me a lot. Thanks
0
 
stl-itAuthor Commented:
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
 
folderolCommented:
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
 
stl-itAuthor Commented:
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
 
folderolCommented:
Your approach is the best, always better to eliminate the nulls at the source, sort of like termites!
0
 
stl-itAuthor Commented:
thanks again
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now