sql-server - TSQL如何指定PIVOT的列列表范围？

Question

下面的 SELECT 语句来 self 用来检索随后发送到 JSON 的数据的存储过程:

SELECT *
FROM (
    SELECT CAST(DateTimeUTC as SmallDateTime) as [DateTime], DataValue, VariableID
    FROM DataValues
    WHERE SiteID = @siteID and VariableID BETWEEN 9 and 10 and DateTimeUTC >= DATEADD (day, @pastDays, getdate())
     ) TableDate
PIVOT (SUM(DataValue) FOR VariableID IN ([9],[10])) PivotTable ORDER BY [DateTime]

我想要完成的是修改过程以接受要旋转的列范围。在上面的示例中，我仅检索两个变量的值。如果我想检索 VariableID 1 到 10 或 1 到 50，该怎么办？必须有一种方法可以通过以下方式检索 1 到 10:

 VariableID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])

Using PIVOT and UNPIVOT MSDN 上没有提到指定范围的方法。

我意识到可以在存储过程中使用动态 SQL，但是考虑到我自己对 SQL 的了解以及那些必须长期维护这一点的人员的知识，我对于是否通过使用动态 SQL 引入额外的复杂性犹豫不决。这里有一个概念上类似的问题，请参阅TSQL Pivot Long List of Columns ，并通过动态 SQL 解决方案进行了回答。

Answer 1

不幸的是，PIVOT 函数无法在不使用动态 SQL 的情况下生成列列表。

因此您的 SQL 代码将与此类似:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(VariableID) 
                    from DataValues
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [DateTime], ' + @cols + ' from 
             (
                SELECT CAST(DateTimeUTC as SmallDateTime) as [DateTime], 
                    DataValue, 
                    VariableID
                FROM DataValues
                WHERE SiteID = '+cast(@siteID as varchar(10))+'
                    -- and VariableID BETWEEN 9 and 10 -- remove the variableID filter
                    and DateTimeUTC >= DATEADD (day, +'cast(@pastDays as varchar(10))+', getdate())
            ) x
            pivot 
            (
                SUM(DataValue)
                for VariableID in (' + @cols + ')
            ) p '

execute(@query)

关键是以下代码，它生成要成为列的 variableIds 列表:

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(VariableID) 
                    from DataValues
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

这将创建所有不同变量ID的列表。然后将该列表添加到要返回的 SQL 查询字符串中。