Write a query to find the top 3 countries with highest suicides for each year from 1985-2016-CodePudding

Pls see the image, i want to get the result as :

Year|First      |Second       |Third
1985|USA - 29446|Japan - 23257|France - 12501
1986|USA - 30892|Japan - 25484|France - 12529
and so on ..... till year 2016.

I know some function will be used to transform the data from row to column format. Just don't know how to do that in SQL Server. I know for Postgres it's CROSSTAB function.

CodePudding user response：

We can use a pivot query with the help of ROW_NUMBER():

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY year
                                 ORDER BY total_suicides DESC) rn
    FROM yourTable
)

SELECT
    year,
    MAX(CASE WHEN rn = 1 THEN country END)   ' - '  
    CAST(MAX(CASE WHEN rn = 1
                  THEN total_suicides END) AS varchar(10)) AS First,
    MAX(CASE WHEN rn = 2 THEN country END)   ' - '  
    CAST(MAX(CASE WHEN rn = 2
                  THEN total_suicides END) AS varchar(10)) AS Second,
    MAX(CASE WHEN rn = 3 THEN country END)   ' - '  
    CAST(MAX(CASE WHEN rn = 3
                  THEN total_suicides END) AS varchar(10)) AS Third
FROM cte
GROUP BY year
ORDER BY year;