Single result set using multiple columns grouped

Question

I am working with the following data

 Name  | Number | Stage
 Bob   | 101    |  1
 James | 102    |  2
 Chris | 57     |  5
 James | 3      |  1
 James | 76     |  1
 Bob   | 95     |  2
 Bob   | 18     |  2
 Bob   | 7      |  3
 Bob   | 11     |  5
 Bob   | 1      |  4

I would like to be able to have results outputted into a format similar to this if possible

Name  | Stage1 | Stage2 | Stage3 | Stage4 | Stage5
Bob   |  1     |  2     | 1      | 1      | 1
Chris |  0     |  0     | 0      | 0      | 5
James |  2     |  1     | 0      | 0      | 0

I know I can get the counts based on each of the stages grouped by the Name using the following

select distinct name, count(stage) [Stage1]
from table
where stage = 1
group by name

But I am lost as to how to combine it into a single result set grouped by name

we are tracking stage progression for people on a 1 - 5 scale — ondrovic, Aug 10 '17 at 14:30
It can be done using `UNION`s (five selects as per your second query, but with `0` for the missing columns), and then a SUM for each of the stage columns. — Peter Abolins, Aug 10 '17 at 14:33
this looks like you want a pivot table showing inclusion/sum for various stages. Take a look at the TSQL Pivot function using a SUM operator. https://stackoverflow.com/questions/11348562/pivoting-with-sum-function-in-tsql — Mark Giaconia, Aug 10 '17 at 14:33

score 3 · Accepted Answer · answered Aug 10 '17 at 14:33

This might be, what you're looking for:

SELECT Name
      ,SUM(IIF(Stage=1,1,0)) AS Stage1
      ,SUM(IIF(Stage=2,1,0)) AS Stage2
      ,SUM(IIF(Stage=3,1,0)) AS Stage3
      ,SUM(IIF(Stage=4,1,0)) AS Stage4
      ,SUM(IIF(Stage=5,1,0)) AS Stage5
  FROM mytable
 GROUP BY Name

score 0 · Answer 2 · answered Aug 10 '17 at 14:39

If you would like pivoting use pivot:

select 
    [Name],
    isnull([1], 0) Stage1,
    isnull([2], 0) Stage2, 
    isnull([3], 0) Stage3, 
    isnull([4], 0) Stage4, 
    isnull([5], 0) Stage5
from table
pivot (count(Number) for Stage in ([1], [2], [3], [4], [5])) p

Single result set using multiple columns grouped

2 Answers2