CODEWITHSUNDEEP
sqlsql-serverdynamicpivot
Steven Marks
Steven Marks

Reputation: 704

SQL Server : Dynamic Pivot

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

Upvotes: 0

Views: 342

Answers (1)

Dimt
Dimt

Reputation: 2328

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'.

Upvotes: 1

Related Questions