SQL Server aggregate rows into column values

Question

first time post on this site but I've used it a lot in the past. I did try to search for an answer to this but couldn't nail down how to frame the question properly, so here goes.

I have a table where each row has an evaluation number of 1, 2, 3 or 4. Each row also has an ID that is a foreign key to a membership table, and another ID that is a foreign key to other data related to the row (a table that holds test information).

What I need to do is filter this list of records by the membership group, and then create an output query of some type that lists:

1. The test ID
2. The number of records that are evaluated to 1
3. The number of records that are evaluated to 2
4. The number of records that are evaluated to 3
5. The number of records that are evaluated to 4

I can work out how to get the count of each evaluation number separately using something like:

SELECT Count(Eval), scores.TestID
FROM membership INNER JOIN (scores INNER JOIN tests ON scores.TestID = tests.TestID) ON membership.ID = scores.ClientID
WHERE membership.GroupID = <MembershipGroup> AND scores.Eval = 1;

Where in the above is the main filter, scores.TestID is the 'grouping' ID, and Count(Eval) is the aggregate I want. Essentially my ideal output is:

ID   |Count1|Count2|Count3|Count4
-----------------------------------
100  |5     |8     |9     |12
101  |16    |2     |14    |11

...and such. Again, sorry if this was a garbled mess, hopefully you lot can help!

PS. The resultant query will be retrieved from ASP.NET to bind to a ListView - if anyone can advise on whether I am better off writing this up as a stored procedure and looking that up or coding it into the SQLDataSource in ASP that would be much appreciated :-)

Answer 1

You should be able to use SUM, CASE and GROUP BY:

SELECT scores.TestID,
    SUM(CASE WHEN Eval = 1 THEN 1 ELSE 0 END) Count1,
    SUM(CASE WHEN Eval = 2 THEN 1 ELSE 0 END) Count2,
    SUM(CASE WHEN Eval = 3 THEN 1 ELSE 0 END) Count3,
    SUM(CASE WHEN Eval = 4 THEN 1 ELSE 0 END) Count4
FROM membership 
    INNER JOIN scores 
        ON membership.ID = scores.ClientID
    INNER JOIN tests ON scores.TestID = tests.TestID
WHERE membership.GroupID = <MembershipGroup>
GROUP BY scores.TestID