PIVOT SQL Server

Question

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)

• cfradenburg: Would it be easier to have a table with three columns; Type Stat, Date, and Value; and then pivot when you're reporting on the data? You can't index based on columns so I'm not sure how performance is going to be after you get a decent number of columns there. And if you're going to start doing averages, maxes, or any other aggregation you'll have to un-pivot it to get them as different rows.

Answer 1

Oracle has that feature (pivot columns from subquery) but not SQL Server. The best you can do is dynamic SQL to generate the PIVOT statement, e.g. SQL Server dynamic PIVOT query.

• András Váczi: Could you direct me to a PostgreSQL example? I know there is crosstab(text,text), which claims to do that, but it does not...
• 孔夫子: You're right. I was 100% certain Oracle has it, but wasn't sure about PostgreSQL so I said "probably", which has now been removed.
• András Váczi: My question was a genuine one for I'd be more than happy to find the solution :)
• wBob: In answer to your question, yes Excel will do this for you. Try the Data tab. You can link directly to a table, then chart your data. Plus, my point is valid; for this example you are wasting your time pivoting in SQL Server. Whilst a kludgy dynamic SQL solution is possible, this kind of data can be charted easily without being pivoted by either Excel or SSRS matrix control. Try it!
• wBob: You're talking about a "generating graphs". And I'm suggesting Excel can do that, easily, straight from your data. SSRS matrix can also deal with this unpivoted.