PivotArenaVoteCounts, Sql Server

Language: Layout:

DECLARE @Votes AS TABLE (arenaId CHAR(6), eventId INT, userId INT, vote TINYINT, active BIT, groupA BIT, groupB BIT)

INSERT INTO @Votes VALUES
('abc123',123,5,0,1,0,0)  
,('abc123',124,5,2,1,0,0)  
,('abc123',125,5,1,1,0,0)  
,('abc123',126,5,1,1,0,0)  
,('abc123',123,6,1,1,1,0)  
,('abc123',124,6,1,1,1,0)  
,('abc123',125,6,2,1,1,0)  
,('abc123',126,6,1,1,1,0)  
,('abc123',123,7,2,1,1,1)  
,('abc123',124,7,1,1,1,1)  
,('abc123',125,7,2,1,1,1)  
,('abc123',126,7,1,1,1,1)  
,('abc123',123,8,2,1,0,1)  
,('abc123',124,8,1,1,0,1)  
,('abc123',125,8,2,1,0,1)  
,('abc123',126,8,2,1,0,1)  
,('def321',127,5,1,1,0,0)  
,('def321',128,5,1,1,0,0)  
,('def321',129,5,2,1,0,0)  
,('def321',130,5,0,1,0,0)  
,('def321',127,6,0,1,1,0)  
,('def321',128,6,2,1,1,0)  
,('def321',129,6,2,1,1,0)  
,('def321',130,6,1,1,1,0)  
,('def321',127,7,1,1,1,1)  
,('def321',128,7,1,1,1,1)  
,('def321',129,7,2,1,1,1)  
,('def321',130,7,2,1,1,1)  
,('def321',127,8,2,1,0,1)  
,('def321',128,8,2,1,0,1)  
,('def321',129,8,2,1,0,1)  
,('def321',130,8,2,1,0,1)

SELECT
    arenaId
    ,COUNT(DISTINCT eventId) as NumberOfEvents
    ,COUNT(CASE WHEN vote = 0 THEN 1 END) as tVotes0
    ,COUNT(CASE WHEN vote = 1 THEN 1 END) as tVotes1
    ,COUNT(CASE WHEN vote = 2 THEN 1 END) as tVotes2
    ,COUNT(CASE WHEN vote = 0 AND groupA = 1 THEN 1 END) as aVotes0
    ,COUNT(CASE WHEN vote = 1 AND groupA = 1 THEN 1 END) as aVotes1
    ,COUNT(CASE WHEN vote = 2 AND groupA = 1 THEN 1 END) as aVotes2
    ,COUNT(CASE WHEN vote = 0 AND groupB = 1 THEN 1 END) as aVotes0
    ,COUNT(CASE WHEN vote = 1 AND groupB = 1 THEN 1 END) as aVotes1
    ,COUNT(CASE WHEN vote = 2 AND groupB = 1 THEN 1 END) as aVotes2
FROM
    @Votes
WHERE
    active = 1
GROUP BY
    arenaId

View schema
Execution time: 0,03 sec, rows selected: 2, rows affected: 32, absolute service time: 0,17 sec