在列子句中带有子查询的MS SQL Server数据透视表 [英] MS SQL Server pivot table with subquery in column clause

问题描述

尽管到目前为止我还没有找到答案，但是我确信这是一种简单的技术！

Im sure this is a simple technique although I can't find an answer so far!

我有

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

等...(大约800个POINTNAMES)

etc... ( for approx 800 POINTNAMES)

有很多点名，我不想在枢轴"FOR"的"IN"子句中列出每个点名 (如下面给出的语法)定义，但可能要使用子查询.

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.

所以我想要的是将所有POINTNAME值作为带有TIMESTAMP AND VALUE列的列，所以我将获得一个TIMESTAMP值，并且每个POINTNAME包含多个列，每个POINTNAME PER TIMESTAMP只有一个值，所以我没有需要聚合任何东西，所以无论如何都选择max?

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?

类似的东西:

SELECT [TIMESTAMP] FROM ( SELECT * FROM POINT_TABLE)
PIVOT( Max[Value] FOR [POINTNAME] IN (SELECT DISTINCT [POINTNAME] FROM POINT_TABLE)

会产生-

   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语法:

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

推荐答案

要获取动态列数，您必须使用动态SQL

for dynamic number of columns you have to use dynamic 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

SQL字段示例

这篇关于在列子句中带有子查询的MS SQL Server数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！