SQL Server 2008 R2
PIVOT IO Stats
I have a script that collects IO stats from sys.dm_io_virtual_file_stats once a day and stocks them in a table. I want to be able to use these stats to generate graphs.
Using PIVOT I have created columns based on the dates the stats were collected
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
Resulting table:
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
As is, I have to add each new date as an additional column.
Question:
Is it possible to generate the PIVOT column list automatically or is there another way to do what I'm thinking of?
Instead of
FOR date_snap IN ([2012-10-17], [2012-10-18], [2012-10-19], [2012-10-20], [2012-10-21]
Something like:
FOR date_snap IN (SELECT DISTINCT date_snap FROM tbl_io_stats)
Update with RichardTheKiwi's answer
Here is the new working query, thank you 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)
