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

And just for the fun of it... (In case you want to get fancy)

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');

DECLARE
    @ColumnList NVARCHAR(4000),
    @sql NVARCHAR(4000),
    @DeBug BIT = 0;

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 
    @ColumnList = CONCAT(@ColumnList, N',
    ', QUOTENAME(ar.City), N' = MAX(CASE WHEN td.City = ', QUOTENAME(ar.City, ''''), N' THEN td.AccidentCount END)')
FROM
    cte_AddRN ar
WHERE 
    ar.RN <= 5
ORDER BY
    ar.RN;

SET @sql = CONCAT(N'
SELECT 
    td.nMonth',
    @ColumnList, N'
FROM 
    #TestData td
GROUP BY 
    td.nMonth;');

IF @DeBug = 1
BEGIN 
    PRINT(@sql);
END;
ELSE
BEGIN  
    EXEC sys.sp_executesql @sql;
END;

Results...

nMonth     New York    Orlando     Los Angeles Memphis     Denver
---------- ----------- ----------- ----------- ----------- -----------
June       36          25          23          18          14

Answer 2

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 3

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