/*
> 3 skaters each compete in an event, and 5 judges give them marks
> which rank
> each against the other:
> Skater #	j1	j2	j3	j4	j5
> 1		3	1	1	1	2
> 2		1	2	2	2	1	
> 3		2	3	3	3	3	
> In this case,
> skater one got more 1st places than skater 2 or 3, so she gets 3/1, 1/2, 1/3
> skater 2 got more 2nd's than 1st, so she gets 3/2, 2/1
> and the last got 4/3, 1/2
> so skater 1 was the best, 2 was second, and 3 was last
> So I want to return for each skater the number of marks (ordinals)
> given by each judge so that I can figure out the placements.
> 
*/
create table #skate_event
	(Skater tinyint, j1 tinyint, j2 tinyint, j3 tinyint, j4 tinyint, j5 tinyint)
insert #skate_event values( 1		,3	,1	,1	,1	,2)
insert #skate_event values( 2		,1	,2	,2	,2	,1)
insert #skate_event values( 3		,2	,3	,3	,3	,3)

SELECT skater, sum(case when rank = 1 then 1 else 0 end) as rank1,
	sum(case when rank = 2 then 1 else 0 end ) as rank2,
	sum(case when rank = 3 then 1 else 0 end) as rank3
FROM (
SELECT skater, j1 FROM #skate_event
UNION ALL
SELECT skater, j2 FROM #skate_event
UNION ALL
SELECT skater, j3 FROM #skate_event
UNION ALL
SELECT skater, j4 FROM #skate_event
UNION ALL
SELECT skater, j5 FROM #skate_event
) AS t(skater, rank)
GROUP BY skater
ORDER BY 1, 2 desc, 3 desc, 4 desc


SELECT Skater,
	cast(rank1 as varchar) + '/1, ' + cast(rank2 as varchar) + '/2, ' +
	cast(rank3 as varchar) + '/3' AS final_ranking
FROM (
SELECT TOP 100 PERCENT
	skater, sum(case when rank = 1 then 1 else 0 end),
	sum(case when rank = 2 then 1 else 0 end ),
	sum(case when rank = 3 then 1 else 0 end)
FROM (
SELECT skater, j1 FROM #skate_event
UNION ALL
SELECT skater, j2 FROM #skate_event
UNION ALL
SELECT skater, j3 FROM #skate_event
UNION ALL
SELECT skater, j4 FROM #skate_event
UNION ALL
SELECT skater, j5 FROM #skate_event
) AS t(skater, rank)
GROUP BY skater
ORDER BY 1, 2 desc, 3 desc, 4 desc
) AS event(skater, rank1, rank2, rank3)