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

Possible duplicate of [SQL Server PIVOT examples?](http://stackoverflow.com/questions/24470/sql-server-pivot-examples) — Juan Carlos Oropeza, Oct 30 '15 at 18:29
sorry i tag the wrong link https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx or this http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Juan Carlos Oropeza, Oct 30 '15 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, Oct 30 '15 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, Oct 30 '15 at 18:40
the real sad thing about it is I started to go this route and my brain just took a dump on me next thing you know my real query (not this example one) ended up being in the ball park of 80 lines of code and now back down to 23 lines. That is usually my red flag when the code is getting way to long I know start searching for simpler answers. — scripter78, Oct 30 '15 at 19:08

score 2 · Answer 1 · answered Oct 30 '15 at 18:31

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

score 1 · Accepted Answer · answered Oct 30 '15 at 18:32

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 '15 at 18:32

Gordon Linoff

1,242,037
58
646
786

Chose this one because it added the else 0 even though it was the second answer it is the most accurate to the question. – scripter78 Oct 30 '15 at 19:04

score 1 · Answer 3 · answered Oct 30 '15 at 18:32

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 '15 at 18:32

Giorgi Nakeuri

35,155
8
47
75

looks like you need `else 0` other wise second column doesnt compute http://sqlfiddle.com/#!3/9aa649/25. the weird part is first one does compute ok. – Juan Carlos Oropeza Oct 30 '15 at 18:43
Ok, that make more sense. And now I know why other answers include `else 0` to avoid `SUM( all null)` http://sqlfiddle.com/#!3/9aa649/26. – Juan Carlos Oropeza Oct 30 '15 at 18:55

SQL Multiple Rows to Single Row Multiple Columns

3 Answers3