Can you pivot and aggregate on two columns with a Sql PIVOT query

Question

Can you pivot two columns in a pivot query.

Suppose I have the following data...

CREATE TABLE JudgeScores 
    (PerformanceID int, 
    JudgeID int, 
    Criteria varchar(10), 
    StrengthScore decimal(9,4), 
    StyleScore decimal(9,4))

--first team performance
--judge1
INSERT INTO JudgeScores (1, 1, "Stunts",        4.2, 1.1)
INSERT INTO JudgeScores (1, 1, "Jumps",         3.9, 0.8)
INSERT INTO JudgeScores (1, 1, "Tumbling",      4.5, 1.0)
INSERT INTO JudgeScores (1, 1, "Choreography",  4.2, 1.5)
--judge2
INSERT INTO JudgeScores (1, 2, "Stunts",        4.1, 1.1)
INSERT INTO JudgeScores (1, 2, "Jumps",         4.0, 0.9)
INSERT INTO JudgeScores (1, 2, "Tumbling",      4.4, 1.1)
INSERT INTO JudgeScores (1, 2, "Choreography",  4.2, 1.6)

--judge3
INSERT INTO JudgeScores (1, 3, "Stunts",        3.8, 1.2)
INSERT INTO JudgeScores (1, 3, "Jumps",         4.2, 0.7)
INSERT INTO JudgeScores (1, 3, "Tumbling",      4.3, 1.2)
INSERT INTO JudgeScores (1, 3, "Choreography",  4.1, 1.3)


--second team performance
--judge1
INSERT INTO JudgeScores (2, 1, "Stunts",        4.3, 1.3)
INSERT INTO JudgeScores (2, 1, "Jumps",         4.0, 0.9)
INSERT INTO JudgeScores (2, 1, "Tumbling",      4.6, 1.1)
INSERT INTO JudgeScores (2, 1, "Choreography",  4.0, 1.0)
--judge2
INSERT INTO JudgeScores (2, 2, "Stunts",        4.1, 1.1)
INSERT INTO JudgeScores (2, 2, "Jumps",         4.0, 0.9)
INSERT INTO JudgeScores (2, 2, "Tumbling",      4.5, 1.2)
INSERT INTO JudgeScores (2, 2, "Choreography",  4.2, 1.6)

--judge3
INSERT INTO JudgeScores (2, 3, "Stunts",        4.1, 1.1)
INSERT INTO JudgeScores (2, 3, "Jumps",         4.5, 0.9)
INSERT INTO JudgeScores (2, 3, "Tumbling",      4.4, 1.2)
INSERT INTO JudgeScores (2, 3, "Choreography",  4.2, 1.6)

I want to select the data so that it will pivot this data as follows

PerformanceID, JudgeID, StuntsStrength, StuntsStyle, JumpsStrength, JumpsStyle, TumbleStrength, TumbleStyle, ChorStrength, ChorStyle
1     1     4.2,    1.1,    3.9,    0.8,   4.5,   1.0,    4.2,     1.5
1     2     4.1,    1.1,    4.0,    0.9,   4.4,   1.1,    4.2,     1.6
...
2     1     4.3,    1.3,    4.0,    0.9,   4.6,   1.1,    4.0,    1.0
2     2     4.1,    1.1,    4.0,    0.9,   4.5,   1.2,    4.2,    1.6
2     3     ...

Can this be done with a pivot query. If not what is the best way to do it?

Answer 1

IMO this is more readable than the pivot equivalent.

Keep it simple and maintainable:

 select PerformanceId,
        JudgeId,
        [StuntsStrength] = avg(case when Criteria = 'Stunts' then StrengthScore else null end),
        [StuntsStyle] = avg(case when Criteria = 'Stunts' then StyleScore else null end),
        [JumpsStrength] = avg(case when Criteria = 'Jumps' then StrengthScore else null end),
        [JumpsStyle] = avg(case when Criteria = 'Jumps' then StyleScore else null end),
        [TumbleStrength] = avg(case when Criteria = 'Tumbling' then StrengthScore else null end),
        [TumbleStyle] = avg(case when Criteria = 'Tumbling' then StyleScore else null end),
        [ChorStrength] = avg(case when Criteria = 'Choreography' then StrengthScore else null end),
        [ChorStyle] = avg(case when Criteria = 'Choreography' then StyleScore else null end)
from    dbo.JudgeScores
group
by      PerformanceId, JudgeId;

Answer 2

You don't need to get as fancy as dynamic sql or the pivot operator to perform this query.

SELECT j.PerformanceID, j.JudgeID, 
    StuntsStrengthScore = (SELECT StrengthScore FROM JudgeScores WHERE PerformanceID = j.PerformanceID and JudgeID = j.JudgeID and Criteria = 'Stunts'),
    StuntsStyleScore = (SELECT StyleScore FROM JudgeScores WHERE PerformanceID = j.PerformanceID and JudgeID = j.JudgeID and Criteria = 'Stunts'),
    JumpsStrengthScore = (SELECT StrengthScore FROM JudgeScores WHERE PerformanceID = j.PerformanceID and JudgeID = j.JudgeID and Criteria = 'Jumps'),
    JumpsStyleScore = (SELECT StyleScore FROM JudgeScores WHERE PerformanceID = j.PerformanceID and JudgeID = j.JudgeID and Criteria = 'Jumps'),
    TumblingStrengthScore = (SELECT StrengthScore FROM JudgeScores WHERE PerformanceID = j.PerformanceID and JudgeID = j.JudgeID and Criteria = 'Tumbling'),
    TumblingStyleScore = (SELECT StyleScore FROM JudgeScores WHERE PerformanceID = j.PerformanceID and JudgeID = j.JudgeID and Criteria = 'Tumbling'),
    ChoreographyStrengthScore = (SELECT StrengthScore FROM JudgeScores WHERE PerformanceID = j.PerformanceID and JudgeID = j.JudgeID and Criteria = 'Choreography'),
    ChoreographyStyleScore = (SELECT StyleScore FROM JudgeScores WHERE PerformanceID = j.PerformanceID and JudgeID = j.JudgeID and Criteria = 'Choreography')
FROM JudgeScores j
ORDER BY PerformanceID, JudgeID