枢轴 SQL Server

SQL 服务器 2008 R2

PIVOT IO 统计数据

我有一个脚本，每天一次从 sys.dm_io_virtual_file_stats 收集 IO 统计数据并将它们存储在一个表中。我希望能够使用这些统计数据来生成图表。

使用 PIVOT 我根据收集统计数据的日期创建了列

select 'read io stall',[2012-10-17], [2012-10-18], [2012-10-19], [2012-10-20], [2012-10-21]
from ( 
    select io_stall_read_ms, date_snap
    from dbo.tbl_io_stats
    where database_name = 'mydb'
    ) up
PIVOT (max(io_stall_read_ms) FOR date_snap IN ([2012-10-17], [2012-10-18], [2012-10-19], [2012-10-20], [2012-10-21]))
AS pvt
UNION
select 'write io stall', [2012-10-17], [2012-10-18], [2012-10-19], [2012-10-20], [2012-10-21]
from ( 
    select database_name, io_stall_write_ms, date_snap
    from dbo.tbl_io_stats
    where database_name = 'mydb'
    ) up
PIVOT (max(io_stall_write_ms) FOR date_snap IN ([2012-10-17], [2012-10-18], [2012-10-19], [2012-10-20], [2012-10-21]))
AS pvt

结果表：

Type Stat   2012-10-17  2012-10-18  2012-10-19  2012-10-20  2012-10-21
read io stall   34449971    34499918    34504701    40383037    40852412
write io stall  20948385    20996323    21001665    24130053    24193110

照原样，我必须将每个新日期添加为附加列。

问题：

是否可以自动生成 PIVOT 列列表，或者是否有其他方法可以实现我的想法？

代替

FOR date_snap IN ([2012-10-17], [2012-10-18], [2012-10-19], [2012-10-20], [2012-10-21]

就像是：

FOR date_snap IN (SELECT DISTINCT date_snap FROM tbl_io_stats)

更新 RichardTheKiwi 的回答

这是新的工作查询，谢谢 RichardTheKiwi

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(ios.date_snap) 
            FROM tbl_io_stats ios
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT database_name + '' read io stall'', ' + @cols + ' from 
            (
                select database_name, io_stall_read_ms, date_snap
                from dbo.tbl_io_stats
                where database_name = ''mydb''
           ) x
            pivot 
            (
                max(io_stall_read_ms)
                for date_snap in (' + @cols + ')
            ) p 
            union
SELECT database_name + '' write io stall'', ' + @cols + ' from 
            (
                select database_name, io_stall_write_ms, date_snap
                from dbo.tbl_io_stats
                where database_name = ''mydb''
           ) x
            pivot 
            (
                max(io_stall_write_ms)
                for date_snap in (' + @cols + ')
            ) p '

--print @query
execute(@query)