How to Pivot/Transpose Top 5 Rows to Columns

Question

I have used Pivot before but I can quite seem to get it to work on this particular data set. Perhaps it's because I am trying to get the top 5 results and pivot at the same time.

Here is a sample of my data (notice the month will stay the same, since I am doing this beforehand):

City            # Of Accidents    Month
----            --------------    -----
Los Angeles     23                June
New York        36                June
Denver          14                June
Memphis         18                June
Orlando         25                June

I would like my result to look like this (the Month, with a column for each of the top cities based on the number of accidents):

Month    TopCity1    TopCity2    TopCity3      TopCity4    TopCity5
-----    --------    --------    --------      --------    --------
June     New York    Orlando     Los Angeles   Memphis     Denver

Thanks in advance!

Answer 1

Another option is PIVOT in concert with Row_Number()

Example

Select *
 From (
        Select Month
              ,City
              ,Col = concat('TopCity',Row_Number() over (Partition By Month Order By [# Of Accidents] Desc) )
         From  YourTable
      ) Src
 Pivot (max(City) for Col in (TopCity1,TopCity2,TopCity3,TopCity4,TopCity5) ) p

Returns

Month   TopCity1    TopCity2    TopCity3        TopCity4    TopCity5
June    New York    Orlando     Los Angeles     Memphis     Denver

Answer 2

This should give you what you're looking for...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    City VARCHAR(20) NOT NULL,
    AccidentCount INT NOT NULL, 
    nMonth VARCHAR(10) NOT NULL 
    );
INSERT #TestData (City, AccidentCount, nMonth) VALUES 
    ('Los Angeles', 23, 'June'),
    ('New York', 36, 'June'),
    ('Denver', 14, 'June'),
    ('Memphis', 18, 'June'),
    ('Orlando', 25, 'June');

WITH 
    cte_AddRN AS (
        SELECT 
            td.City, td.AccidentCount, td.nMonth,
            RN = ROW_NUMBER() OVER (PARTITION BY td.nMonth ORDER BY td.AccidentCount DESC)
        FROM
            #TestData td
        )
SELECT 
    ar.nMonth,
    TopCity1 = MAX(CASE WHEN ar.RN = 1 THEN ar.City END),
    TopCity2 = MAX(CASE WHEN ar.RN = 2 THEN ar.City END),
    TopCity3 = MAX(CASE WHEN ar.RN = 3 THEN ar.City END),
    TopCity4 = MAX(CASE WHEN ar.RN = 4 THEN ar.City END),
    TopCity5 = MAX(CASE WHEN ar.RN = 5 THEN ar.City END)
FROM
    cte_AddRN ar
GROUP BY
    ar.nMonth;

Results...

nMonth     TopCity1             TopCity2             TopCity3             TopCity4             TopCity5
---------- -------------------- -------------------- -------------------- -------------------- --------------------
June       New York             Orlando              Los Angeles          Memphis              Denver