I have a question on how to express the output of some results? The problem is: There were a number of contests where participants each made number of attempts. The attempt with the highest score is only one considered. Write a query to list the contestants ranked in the top 3 for each contest. If multiple contestants have the same score in each contest, they are at the same rank.

Report event_id, rank 1 name(s), rank 2 name(s), rank 3 name(s). Order the contest by event_id. Name that share a rank should be ordered alphabetically and separated by a comma.

The database consists in only one table is:

My Query is:

WITH max_score AS (
  SELECT event_id, participant_name, CAST(MAX(ROUND(score, 2,1)) AS DECIMAL (18,2)) AS score
  FROM scoretable 
  GROUP BY event_id, participant_name
),
Rank_table AS( 
  SELECT 
       event_id, 
       participant_name,
       score,
       DENSE_RANK() OVER   
        (PARTITION BY event_id ORDER BY score DESC) AS FinalRank
FROM max_score
)
SELECT * FROM Rank_table
WHERE FinalRank <= 3
ORDER BY event_id, score DESC;

So I can make the results rank, my question is what resource could I use to make the result look like this:

Any help would be appreciated!

CodePudding user response：

Use a pivot query to generate the 3 output columns you want:

WITH cte AS (
    SELECT *, DENSE_RANK() OVER (PARTITION BY event_id ORDER BY FinalRank) dr
    FROM Rank_table
)

SELECT event_id,
       STRING_AGG(CASE WHEN dr = 1 THEN participant_name END, ',')
           WITHIN GROUP (ORDER BY participant_name) AS [Rank 1],
       STRING_AGG(CASE WHEN dr = 2 THEN participant_name END, ',')
           WITHIN GROUP (ORDER BY participant_name) AS [Rank 2],
       STRING_AGG(CASE WHEN dr = 3 THEN participant_name END, ',')
           WITHIN GROUP (ORDER BY participant_name) AS [Rank 3]
FROM cte
ORDER BY event_id;