SQL Server：动态数据透视 [英] SQL Server : Dynamic Pivot

问题描述

我已经发现了许多有关动态枢轴的文章，首先选择数据，然后运行第二个查询来完成枢轴，但是我有一个很大的查询需要一个动态的枢纽，我不知道如何使其运行。每个主机上的服务器可以改变，这就是为什么我需要它是动态的。可能有一个非常简单的修复，但我不是很好的SQL Server。

这是迄今为止的代码：

  DECLARE @ StartTime Date 
 DECLARE @EndTime日期
 
 SET @StartTime ='2014-08-19 23:00:00'
 SET @EndTime ='2014-08-22 09： 21：33.557'
 
 SELECT * 
 FROM 
（SELECT 
 [T1]。[AvgMemoryUsage] AS C2，
 CONVERT（varchar，[T1] 。[DateTime]，105）AS C3，
 [T2]。[名称] AS C4 
从
 dbo.VIM_VMStatistics AS T1 
 INNER JOIN 
 dbo.VIM_VirtualMachineNodes AS T2 ON [T1]。[VirtualMachineID] = [T2]。[VirtualMachineID] 
 INNER JOIN 
 dbo.VIM_HostNodes AS T3 ON [T2]。[HostID] = [T3]。[HostID] 
 LEFT JOIN 
 dbo.Nodes AS T8 ON [T3]。[NodeID] = [T8]。[NodeID] 
 WHERE 
 [T8]。[caption] ='VMWARE- $ [$] $ [$] $ [$] $ [$] $ [$] $ [$] $ [$] > = @StartTime 
 AND [T1]。[DateTime]< = @EndTime 
 
 UNION ALL 
 
（SELECT 
 [T4]。[AvgMemoryUsage] AS C6，
 CONVERT（varchar，[T4]。[DateTime]，105）AS C7，
 [T5]。[名称] AS C8 
从
 dbo.VIM_VMStatistics AS T4 
 INNER JOIN 
 dbo.VIM_VirtualMachineNodes AS T5 ON [T4]。[VirtualMachineID] = [T5]。[VirtualMachineID] 
 INNER JOIN 
 dbo.VIM_HostNodes AS T6 ON [T5]。[HostID] = [T6]。[HostID] 
 LEFT JOIN 
 dbo.Nodes AS T7 ON [T5]。[NodeID] = [T7]。[NodeID] 
 WHERE 
 [T6 ] [NodeID] = 1492 
 AND [T5]。[PowerState] ='poweredOn'
 AND（[T5]。[NodeID] IS NOT NULL）
 AND [T4] DateTime]> = @StartTime AND [T4]。[DateTime]< = @EndTime）
）AS SourceTable 
 PIVOT（
 AVG（[c2]）
 FOR [ C4] IN（[server1]，[server2]，[server3]，[server8]，[server12]）
）AS数据透视表
  

FOR XML PATH 和 QUOTENAME（）：

  DECLARE @strPivotColumns nvarchar（max）
 SELECT @strPivotColumns = STUFF（（SELECT'，'+ QUOTENAME（[Name]）
 FROM dbo .VIM_VirtualMachineNodes FOR XML PATH（''）），1，1，''）
  

然后你需要将原始查询保存为字符串并将字符串与列名称进行连接：

  DECLARE @StartTime日期
 DECLARE @ EndTime Date 
 
 SET @StartTime ='2014-08-19 23:00:00'
 SET @EndTime ='2014-08-22 09：21：33.557'
 
 DECLARE @DynamicPivotQuery nvarchar（2000）
 SET @DynamicPivotQuery = 
'SELECT * FROM（
 SELECT [T1]。[AvgMemoryUsage] AS C2，CONVERT（varchar，[T1] 。[DateTime]，105）AS C3，[T2]。[名称] AS C4 
 FROM dbo.VIM_VMStatistics AS T1 
 INNER JOIN dbo.VIM_VirtualMachineNodes AS T2 ON [T1]。[VirtualMachineID] = [ T2]。[VirtualMachineID] 
 INNER JOIN dbo.VIM_HostNodes AS T3 ON [T2]。[HostID] = [T3]。[HostID] 
 LEFT JOIN dbo.Nodes AS T8 ON [T3]。[NodeID] = [T8] NodeID] 
 WHERE [T8]。[caption] =''VMWARE-ESX01.Server.Local''AND [T2]。[PowerState] =''poweredOn''AND（[T2]。[NodeID] IS NULL）AND [T1]。[DateTime]> = @StartTime AND [T1]。[DateTime] <= @EndTime 
 UNION ALL 
（
 SELECT [T4]。[AvgMemoryUsage ] AS C6，CONVERT（varchar，[T4]。[DateTime]，105）AS C7，[T5]。[名称] AS C8 
 FROM dbo.VIM_VMStatistics AS T4 
 INNER JOIN dbo.VIM_VirtualMachineNodes AS T5 ON [T4]。[VirtualMachineID] = [T5]。[VirtualMachineID] 
 INNER JOIN dbo.VIM_HostNodes AS T6 ON [T5]。[HostID] = [T6]。[HostID] 
 LEFT JOIN dbo.Nodes AS T7 ON [T5]。[NodeID] = [T7]。[NodeID] 
 WHERE [T6]。[NodeID] = 1492 AND [T5]。[PowerState] =poweredOnAND （[T5]。[NodeID] IS NOT NULL）AND [T4]。[DateTime]> = @StartTime AND [T4]。[DateTime]< = @EndTime 
）
）AS SourceTable 
 PIVOT（
 AVG（[c2]）
 FOR [C4] IN（'+ @strPivotColumns +'）
）AS数据透视表'
  

然后，您可以执行查询字符串：

  EXEC sp_executesql @DynamicPivotQuery，N'@ StartTime date，@EndTime date'，@StartTime = @StartTime，@EndTime = @EndTime 
  

EDIT BY STEVE：以下是目前唯一引起报价问题的问题：

 code> FOR [C4] IN（'+ @strPivotColumns +'）
  

错误：
消息105，级别15，状态1，行20
字符串'mad-a'后取消闭合的引号。
消息102，级别15，状态1，行20
'mad-a'附近的语法不正确。

I have found many articles on dynamic pivots, selecting the data first and then running a 2nd query to do the pivot, however I have a large query that I require a dynamic pivot and am not sure how to get it working. The servers on each host can change this is why I need it to be dynamic. There is probably a very simple fix but I'm not great with SQL Server.

This is the code I have so far:

DECLARE @StartTime Date
DECLARE @EndTime Date

SET @StartTime = '2014-08-19 23:00:00'
SET @EndTime = '2014-08-22 09:21:33.557'

SELECT * 
FROM 
    (SELECT 
        [T1].[AvgMemoryUsage] AS C2, 
        CONVERT(varchar, [T1].[DateTime], 105) AS C3, 
        [T2].[Name] AS C4
     FROM 
        dbo.VIM_VMStatistics AS T1
     INNER JOIN 
        dbo.VIM_VirtualMachineNodes AS T2 ON [T1].[VirtualMachineID] = [T2].[VirtualMachineID]
     INNER JOIN 
        dbo.VIM_HostNodes AS T3 ON [T2].[HostID] = [T3].[HostID]
     LEFT JOIN 
        dbo.Nodes AS T8 ON [T3].[NodeID] = [T8].[NodeID]
     WHERE 
        [T8].[caption] = 'VMWARE-ESX01.Server.Local' 
        AND [T2].[PowerState] = 'poweredOn' 
        AND ([T2].[NodeID] IS NULL) 
        AND [T1].[DateTime] >= @StartTime 
        AND [T1].[DateTime] <= @EndTime

    UNION ALL

    (SELECT 
        [T4].[AvgMemoryUsage] AS C6, 
        CONVERT(varchar, [T4].[DateTime], 105) AS C7, 
        [T5].[Name] AS C8
     FROM 
        dbo.VIM_VMStatistics AS T4
     INNER JOIN 
        dbo.VIM_VirtualMachineNodes AS T5 ON [T4].[VirtualMachineID] = [T5].[VirtualMachineID]
     INNER JOIN 
        dbo.VIM_HostNodes AS T6 ON [T5].[HostID] = [T6].[HostID]
     LEFT JOIN 
        dbo.Nodes AS T7 ON [T5].[NodeID] = [T7].[NodeID]
     WHERE 
        [T6].[NodeID] = 1492 
        AND [T5].[PowerState] = 'poweredOn' 
        AND ([T5].[NodeID] IS NOT NULL) 
        AND [T4].[DateTime] >= @StartTime AND [T4].[DateTime] <= @EndTime)
) AS SourceTable 
PIVOT(
    AVG([c2])
    FOR [C4] IN ([server1],[server2],[server3],[server8],[server12])
) AS PivotTable

解决方案

To build a dynamic pivot is fairly simple when you already know how pivot works. First you need to have a string with delimited columns to be pivoted. To achieve that you can use recommended FOR XML PATH and QUOTENAME():

DECLARE @strPivotColumns nvarchar(max)
SELECT @strPivotColumns = STUFF((SELECT ','  +QUOTENAME([Name]) 
                   FROM dbo.VIM_VirtualMachineNodes FOR XML PATH('')), 1, 1, '')

Then you need to save the original query as a string and concatenate the string with column names:

DECLARE @StartTime Date
DECLARE @EndTime Date

SET @StartTime = '2014-08-19 23:00:00'
SET @EndTime = '2014-08-22 09:21:33.557'

DECLARE @DynamicPivotQuery nvarchar(2000)
SET @DynamicPivotQuery = 
'SELECT * FROM (
SELECT [T1].[AvgMemoryUsage] AS C2, CONVERT(varchar, [T1].[DateTime],105) AS C3, [T2].[Name] AS C4
FROM dbo.VIM_VMStatistics AS T1
INNER JOIN dbo.VIM_VirtualMachineNodes AS T2 ON [T1].[VirtualMachineID] = [T2].[VirtualMachineID]
INNER JOIN dbo.VIM_HostNodes AS T3 ON [T2].[HostID] = [T3].[HostID]
LEFT JOIN dbo.Nodes AS T8 ON [T3].[NodeID] = [T8].[NodeID]
WHERE [T8].[caption] = ''VMWARE-ESX01.Server.Local'' AND [T2].[PowerState] = ''poweredOn'' AND ([T2].[NodeID] IS NULL) AND [T1].[DateTime] >= @StartTime AND [T1].[DateTime] <= @EndTime
    UNION ALL
    (
        SELECT [T4].[AvgMemoryUsage] AS C6, CONVERT(varchar, [T4].[DateTime],105) AS C7, [T5].[Name] AS C8
        FROM dbo.VIM_VMStatistics AS T4
        INNER JOIN dbo.VIM_VirtualMachineNodes AS T5 ON [T4].[VirtualMachineID] = [T5].[VirtualMachineID]
        INNER JOIN dbo.VIM_HostNodes AS T6 ON [T5].[HostID] = [T6].[HostID]
        LEFT JOIN dbo.Nodes AS T7 ON [T5].[NodeID] = [T7].[NodeID]
        WHERE [T6].[NodeID] = 1492 AND [T5].[PowerState] = ''poweredOn'' AND ([T5].[NodeID] IS NOT NULL) AND [T4].[DateTime] >= @StartTime AND [T4].[DateTime] <= @EndTime
    )
)AS SourceTable 
PIVOT(
    AVG([c2])
    FOR [C4] IN (' + @strPivotColumns + ')
) AS PivotTable'

Then you can execute your query string:

EXEC sp_executesql @DynamicPivotQuery, N'@StartTime date, @EndTime date', @StartTime = @StartTime, @EndTime = @EndTime

EDIT BY STEVE: The following is the only thing now causing an issue with quotations:

FOR [C4] IN (' + @strPivotColumns + ')

ERROR: Msg 105, Level 15, State 1, Line 20 Unclosed quotation mark after the character string 'mad-a'. Msg 102, Level 15, State 1, Line 20 Incorrect syntax near 'mad-a'.

这篇关于SQL Server：动态数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！