Double group by

Question

I've got table with two columns: name and grade. It looks sth like this:

NAME  | GRADE
Adam  | 1
Adam  | 2
Adam  | 2
Adam  | 3
Frank | 2
Frank | 1

Now I want create view that will looks like this:

NAME  | GRADE 1 | GRADE 2 | GRADE 3
Adam  |    1    |    2    |     1
Frank |    1    |    1    |     0

I've wrote this:

SELECT     Name, 
    (SELECT COUNT(Grade) 
    FROM dbo.Rodzaj 
    WHERE Grade = '1') as Grade_1,  
    (SELECT COUNT(Grade) 
    FROM dbo.Rodzaj 
    WHERE Grade = '2) as Grade_2,  
    (SELECT COUNT(Grade) 
    FROM dbo.Rodzaj 
    WHERE Grade = '3') as Grade_3
FROM         dbo.Rodzaj
GROUP BY Name

but it doesn't work... I would appreciate any help

Answer 1

What you are looking for is called a "pivot table" and it is done with a chain of CASE statements which apply a 1 or 0 for each condition, then SUM() up the ones and zeros to retrieve a count.

SELECT
  NAME,
  SUM(CASE WHEN GRADE = 1 THEN 1 ELSE 0 END) AS GRADE1,
  SUM(CASE WHEN GRADE = 2 THEN 1 ELSE 0 END) AS GRADE2,
  SUM(CASE WHEN GRADE = 3 THEN 1 ELSE 0 END) AS GRADE3
FROM Rodzaj
GROUP BY NAME

Note that if you need this to have a dynamic number of columns, you will have to construct the query using a scripting language and a loop. (Or a loop inside a stored procedure)