列子句中带有子查询的 MS SQL Server 数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13245364/
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
MS SQL Server pivot table with subquery in column clause
提问by user1801843
Im sure this is a simple technique although I can't find an answer so far!
我确定这是一个简单的技术，尽管到目前为止我找不到答案！
I have
我有
TIMESTAMP | POINTNAME | VALUE
2012-10-10 16:00:00 AHU01 20
2012-10-10 16:00:00 AHU02 25
2012-10-10 16:00:15 AHU01 26
2012-10-10 16:00:15 AHU02 35
etc... ( for approx 800 POINTNAMES)
等等...（大约 800 个点名）
with many pointnames I dont want to list each one in the 'IN' clause of the pivot 'FOR' (as syntax given below) definition but would like to use perhaps a subquery.
有许多点名，我不想在数据透视“FOR”（如下文给出的语法）定义的“IN”子句中列出每个点名，但可能想使用子查询。
So what I would like is all the POINTNAME values as columns with A TIMESTAMP AND VALUE column, so I will get one TIMESTAMP value and many columns with each POINTNAME, there is only one value per POINTNAME PER TIMESTAMP so I don't need to aggregate anything so just choose max anyway?
所以我想要的是所有 POINTNAME 值作为带有 TIMESTAMP AND VALUE 列的列，所以我会得到一个 TIMESTAMP 值和许多列，每个 POINTNAME，每个 POINTNAME PER TIMESTAMP 只有一个值，所以我不需要聚合有什么所以就选择 max 吗？
Something like:
就像是：
SELECT [TIMESTAMP] FROM ( SELECT * FROM POINT_TABLE)
PIVOT( Max[Value] FOR [POINTNAME] IN (SELECT DISTINCT [POINTNAME] FROM POINT_TABLE)
would produce-
会产生——
TIMESTAMP AHU01 AHU02
2012-10-10 16:00:00 20 25
2012-10-10 16:15:00 26 35
I realise it is probably no this simple but hopefully you get what I'm trying to achieve?
我意识到这可能不是那么简单，但希望你能得到我想要实现的目标？
PIVOT SYNTAX:
枢轴语法：
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
回答by Roman Pekar
for dynamic number of columns you have to use dynamic SQL
对于动态列数，您必须使用动态 SQL
declare
@cols nvarchar(max),
@stmt nvarchar(max)
select @cols = isnull(@cols + ', ', '') + '[' + T.POINTNAME + ']' from (select distinct POINTNAME from TABLE1) as T
select @stmt = '
select *
from TABLE1 as T
pivot
(
max(T.VALUE)
for T.POINTNAME in (' + @cols + ')
) as P'
exec sp_executesql @stmt = @stmt
