在 SQL Server 2008 R2 中使用 PIVOT 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13011350/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using PIVOT table with SQL Server 2008 R2
提问by Razwan
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 val1so my output will look like:
从以下结构中，我想根据列旋转数据，val1因此我的输出将如下所示：
Jan Jan Feb March
70 12 12 14
回答by GarethD
You will need to use Dynamic SQL I would have thought (unless your columns will remain static)
您将需要使用我原以为的动态 SQL（除非您的列将保持静态）
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 BYin the ROW_NUMBERfunction.
所以你可能需要鼓捣出了ORDER BY在ROW_NUMBER函数。
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_NUMBERwill need to also be reflected in the ORDER BYclause in the query that generates the columns names and the pivot list:
同样， 中的任何更改ROW_NUMBER也需要反映在ORDER BY生成列名称和数据透视表的查询中的子句中：
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;
回答by Deepak
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 dis one..
试一试..
