SQL Multiple Rows to Single Row Multiple Columns

Question

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.

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

sorry i tag the wrong link technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx or this stackoverflow.com/questions/15931607/… — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Oct 30, 2015 at 18:30
Wholly heck how the hell was this even answered so quickly. even if it was an easy answer it seemed like as soon as I hit submit 3 answers were already posted. — scripter78
– scripter78, Commented Oct 30, 2015 at 18:36
@scripter78, when you will answer 5 such a question per day it becomes automatic. You see the question and your brain is not thinking any more. You are doing it in auto mode. This is the old standard trick in SQl. — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Oct 30, 2015 at 18:40

Vamsi Prabhala · Accepted Answer · 2015-10-30 18:31:57Z

2

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

answered Oct 30, 2015 at 18:31

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2015-10-30 18:32:01Z

1

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;

answered Oct 30, 2015 at 18:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

scripter78 Over a year ago

Chose this one because it added the else 0 even though it was the second answer it is the most accurate to the question.

Giorgi Nakeuri · Accepted Answer · 2015-10-30 18:32:03Z

1

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

answered Oct 30, 2015 at 18:32

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

2 Comments

Juan Carlos Oropeza Over a year ago

looks like you need else 0 other wise second column doesnt compute sqlfiddle.com/#!3/9aa649/25. the weird part is first one does compute ok.

Juan Carlos Oropeza Over a year ago

Ok, that make more sense. And now I know why other answers include else 0 to avoid SUM( all null) sqlfiddle.com/#!3/9aa649/26.

Collectives™ on Stack Overflow

SQL Multiple Rows to Single Row Multiple Columns

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related