Using a PIVOT Table with SQL Server 2008 R2

Question

I ran into a problem that I can’t solve and tried everything. I hope I 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 expand the data based on the val1 column, so my output would look like this:

 Jan Jan Feb March 70 12 12 14 

Answer 1

You will need to use the dynamic SQL that I was thinking about (unless your columns remain static)

The query to be completed is as follows:

 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'm not sure why this should be

 Jan Jan Feb March 70 12 12 14 

but not

 Jan Jan Feb March 12 70 12 14 

therefore, you may need to bother with ORDER BY in the ROW_NUMBER function.

And to build it 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 to ROW_NUMBER should also be reflected in the ORDER BY in the query that generates the column 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; 

Answer 2

This works for me:

 SELECT * FROM (select COUNT(isnull(intFileStatus,0)) as FileCount , case cast(intfilestatus as nvarchar(25)) when '0' then 'Not Allocated' when '1' then 'Assigned' when '2' then 'Pending' when '3' then 'Send For Qc' when '4' then 'Allocated To Qc' when '5' then 'Finish Qc' when '7' then 'Delivered' else cast(isnull(intFileStatus,0) as nvarchar (25)) end FileStatusfrom dbo.tblLPO_FileDetails group by intFileStatus ) AS original PIVOT ( MIN(FileCount) FOR [FileStatus] IN ([Not Allocated], [Assigned], [Pending],[Send For Qc],[Allocated To Qc],[Finish Qc],[Delivered]) ) AS PivotTable 

Try one of them.