asp.net - SQL Server aggregate rows into column values -

first time post on site i've used lot in past. did try search answer couldn't nail down how frame question properly, here goes.

i have table each row has evaluation number of 1, 2, 3 or 4. each row has id foreign key membership table, , id foreign key other data related row (a table holds test information).

what need filter list of records membership group, , create output query of type lists:

1. the test id
2. the number of records evaluated 1
3. the number of records evaluated 2
4. the number of records evaluated 3
5. the number of records evaluated 4

i can work out how count of each evaluation number separately using like:

select count(eval), scores.testid membership inner join (scores inner join tests on scores.testid = tests.testid) on membership.id = scores.clientid membership.groupid = <membershipgroup> , scores.eval = 1; 

where in above main filter, scores.testid 'grouping' id, , count(eval) aggregate want. ideal output is:

 id   |count1|count2|count3|count4 ----------------------------------- 100  |5     |8     |9     |12 101  |16    |2     |14    |11 

...and such. again, sorry if garbled mess, lot can help!

ps. resultant query retrieved asp.net bind listview - if can advise on whether better off writing stored procedure , looking or coding sqldatasource in asp appreciated :-)

you should able use sum, case , group by:

select scores.testid,     sum(case when eval = 1 1 else 0 end) count1,     sum(case when eval = 2 1 else 0 end) count2,     sum(case when eval = 3 1 else 0 end) count3,     sum(case when eval = 4 1 else 0 end) count4 membership      inner join scores          on membership.id = scores.clientid     inner join tests on scores.testid = tests.testid membership.groupid = <membershipgroup> group scores.testid