I get to the same result, but I might be missing information from you.

Will you always have 2 IDENT1 values? How would you show that?

For additional values, you might need to use some dynamic sql, but I'm uncertain without further information.

WITH SampleData(CATEGORY, Question_ID, ANSWER_VALUE, IDENT1) AS(

SELECT 61, 533, 2, 45350 UNION ALL

SELECT 61, 534, 0, 45350 UNION ALL

SELECT 61, 535, 1, 45350 UNION ALL

SELECT 61, 536, 2, 45350 UNION ALL

SELECT 61, 537, 1, 45350 UNION ALL

SELECT 61, 538, 0, 45350 UNION ALL

SELECT 61, 539, 0, 45350 UNION ALL

SELECT 61, 540, 0, 45350 UNION ALL

SELECT 61, 541, 0, 45350 UNION ALL

SELECT 61, 542, 0, 45350 UNION ALL

SELECT 62, 543, 0, 45350 UNION ALL

SELECT 62, 544, 0, 45350 UNION ALL

SELECT 62, 545, 0, 45350 UNION ALL

SELECT 62, 546, 1, 45350 UNION ALL

SELECT 62, 547, 0, 45350 UNION ALL

SELECT 62, 548, 0, 45350 UNION ALL

SELECT 62, 549, 0, 45350 UNION ALL

SELECT 61, 533, 2, 52583 UNION ALL

SELECT 61, 534, 0, 52583 UNION ALL

SELECT 61, 535, 1, 52583 UNION ALL

SELECT 61, 536, 2, 52583 UNION ALL

SELECT 61, 537, 1, 52583 UNION ALL

SELECT 61, 538, 0, 52583 UNION ALL

SELECT 61, 539, 0, 52583 UNION ALL

SELECT 61, 540, 0, 52583 UNION ALL

SELECT 61, 541, 0, 52583 UNION ALL

SELECT 61, 542, 0, 52583 UNION ALL

SELECT 62, 544, 0, 52583 UNION ALL

SELECT 62, 545, 0, 52583 UNION ALL

SELECT 62, 546, 1, 52583 UNION ALL

SELECT 62, 547, 0, 52583 UNION ALL

SELECT 62, 548, 0, 52583 UNION ALL

SELECT 62, 549, 0, 52583

),

Scores AS(

SELECT *, DENSE_RANK() OVER(ORDER BY IDENT1) ranked

FROM SampleData

)

SELECT CATEGORY,

AVG(CASE WHEN ANSWER_VALUE IN (0,1,2,3) AND ranked = 1

THEN ANSWER_VALUE*1.0 END)* 10.0 AS Score1,

AVG(CASE WHEN ANSWER_VALUE IN (0,1,2,3) AND ranked = 2

THEN ANSWER_VALUE*1.0 END)* 10.0 AS Score2

FROM Scores

GROUP BY CATEGORY