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