CODEWITHSUNDEEP
sqlt-sql
ondrovic
ondrovic

Reputation: 1175

Single result set using multiple columns grouped

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

Upvotes: 2

Views: 69

Answers (2)

Deadsheep39
Deadsheep39

Reputation: 611

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

Upvotes: 0

Esteban P.
Esteban P.

Reputation: 2809

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

Upvotes: 3

Related Questions