Using PIVOT table with SQL Server 2008 R2

pivot, sql, sql-server-2008-r2

Solution

You will need to use Dynamic SQL I would have thought (unless your columns will remain static)

The query that needs to be executed is:

SELECT  [Jan] = [Jan1], 
        [Jan] = [Jan2], 
        [Feb] = [Feb1], 
        [March] = [March1]
FROM    (   SELECT  [PivotColumn] = [Month] + CONVERT(VARCHAR(10), ROW_NUMBER() OVER(PARTITION BY [Month] ORDER BY ID)), 
                    Val1
            FROM    T
        ) t
        PIVOT
        (   MAX(Val1)
            FOR [PivotColumn] IN ([Jan1], [Jan2], [Feb1], [March1])
        ) pvt

Although I am not sure why it should be

Jan Jan Feb March
70  12  12  14

and not

Jan Jan Feb March
12  70  12  14

so you may need to tinker with the `ORDER BY` in the `ROW_NUMBER` function.

And to build this dynamically you can use:

-- CREATE SAMPLE TABLE AND INSERT DATA
CREATE TABLE #T (ID INT, Month VARCHAR(10), Val1 INT, Val2 INT, Val3 DECIMAL(5, 1));
INSERT #T VALUES (1, 'Jan', 70, 80, 90), (2, 'Jan', 12, 13, 15), (3, 'Feb', 12, 67, 99), (4, 'March', 14, 15, 17.1);

-- DECLARE VARIABLES TO STORE THE COLUMN NAMES
DECLARE @PivotList NVARCHAR(MAX) = '',
        @ColumnList NVARCHAR(MAX) = '';

-- HERE USE ROW_NUMBER() TO UNIQUELY IDENTIFY VALUES FOR MONTHS 
-- THIS MEANS JAN: 70 AND JAN: 12 CAN BE IDENTIFIED SEPARATELY LATER, BUT RETAIN THE DUPLICATE COLUMN NAME [Jan]
SELECT  @ColumnList = @ColumnList + ', ' + QUOTENAME([Month]) + ' = ' + QUOTENAME([Month] + CONVERT(VARCHAR(10), ROW_NUMBER() OVER(PARTITION BY [Month] ORDER BY ID))),
        @PivotList = @PivotList + ', ' + QUOTENAME([Month] + CONVERT(VARCHAR(10), ROW_NUMBER() OVER(PARTITION BY [Month] ORDER BY ID)))
FROM    #T
ORDER BY ID;

DECLARE @SQL NVARCHAR(MAX) = 'SELECT ' + STUFF(@ColumnList, 1, 2, '') + '
                            FROM    (   SELECT  [PivotColumn] = [Month] + CONVERT(VARCHAR(10), ROW_NUMBER() OVER(PARTITION BY [Month] ORDER BY ID)), 
                                                Val1
                                        FROM    #T
                                    ) t
                                    PIVOT
                                    (   MAX(Val1)
                                        FOR [PivotColumn] IN (' + STUFF(@PivotList, 1, 2, '') + ')
                                    ) pvt';

EXECUTE SP_EXECUTESQL @SQL;

DROP TABLE #T;

Again any changes in the `ROW_NUMBER` will need to also be reflected in the `ORDER BY` clause in the query that generates the columns names and the pivot list:

SELECT  @ColumnList = @ColumnList + ', ' + QUOTENAME([Month]) + ' = ' + QUOTENAME([Month] + CONVERT(VARCHAR(10), ROW_NUMBER() OVER(PARTITION BY [Month] ORDER BY ID))),
        @PivotList = @PivotList + ', ' + QUOTENAME([Month] + CONVERT(VARCHAR(10), ROW_NUMBER() OVER(PARTITION BY [Month] ORDER BY ID)))
FROM    #T
ORDER BY ID;

Problem

I have come across an issue which I cannot resolve and have tried everything. Hope I can get an answer here. My data in my table is as follows ``` Id Month Val1 Val2 Val3 1 Jan 70 80 90 2 Jan 12 13 15 3 Feb 12 67 99 4 March 14 15 17.1 ``` From the following structure I would like to pivot the data based on column `val1` so my output will look like: ``` Jan Jan Feb March 70 12 12 14 ```

Original source