Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
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. –  cfradenburg Oct 18 '12 at 12:38

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Could you direct me to a PostgreSQL example? I know there is crosstab(text,text), which claims to do that, but it does not... –  dezso Oct 18 '12 at 13:22
    
Thanks that's what I was looking for –  Craig Efrein Oct 18 '12 at 13:44
    
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. –  孔夫子 Oct 18 '12 at 13:45
    
My question was a genuine one for I'd be more than happy to find the solution :) –  dezso Oct 18 '12 at 13:51
    
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 Oct 18 '12 at 14:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.