CODEWITHSUNDEEP
sqljoin
scripter78
scripter78

Reputation: 1177

SQL Multiple Rows to Single Row Multiple Columns

I am including a SQLFiddle to show as an example of where I am currently at. In the example image you can see that simply grouping you get up to two lines per user depending on their status and how many of those statuses they have.

enter image description here

http://sqlfiddle.com/#!3/9aa649/2

The way I want it to come out is to look like the image below. Having a single line per user with two totaling columns one for Fail Total and one for Pass Total. I have been able to come close but since BOB only has Fails and not Passes this query leaves BOB out of the results. which I want to show BOB as well with his 6 Fail and 0 Pass

    select a.PersonID,a.Name,a.Totals as FailTotal,b.Totals as PassTotals from (
 select PersonID,Name,Status, COUNT(*) as Totals from UserReport
 where Status = 'Fail'
group by PersonID,Name,Status) a
join 
(
 select PersonID,Name,Status, COUNT(*) as Totals from UserReport
 where Status = 'Pass'
group by PersonID,Name,Status) b
on a.PersonID=b.PersonID

The below picture is what I want it to look like. Here is another SQL Fiddle that shows the above query in action http://sqlfiddle.com/#!3/9aa649/13

enter image description here

Upvotes: 0

Views: 110

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

With conditional aggregation:

select PersonID, 
       Name,
       sum(case when Status = 'Fail' then 1 end) as Failed,
       sum(case when Status = 'Passed' then 1 end) as Passed
from UserReport
group by PersonID, Name

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use conditional aggregation:

select PersonID, Name,
       sum(case when Status = 'Fail' then 1 else 0 end) as FailedTotal,
       sum(case when Status = 'Pass' then 1 else 0 end) as PassedTotal
from UserReport
group by PersonID, Name;

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use conditional aggregation if the number of values for status column is fixed.

Fiddle

select PersonID,Name, 
sum(case when "status" = 'Fail' then 1 else 0 end) as failedtotal,
sum(case when "status" = 'Pass' then 1 else 0 end) as passedtotals 
from UserReport
group by PersonID,Name

Upvotes: 2

Related Questions