By Craig Efrein


2012-10-18 11:03:59 8 Comments

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)

1 comments

@孔夫子 2012-10-18 13:16:36

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.

@dezso 2012-10-18 13:22:38

Could you direct me to a PostgreSQL example? I know there is crosstab(text,text), which claims to do that, but it does not...

@孔夫子 2012-10-18 13:45:30

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.

@dezso 2012-10-18 13:51:04

My question was a genuine one for I'd be more than happy to find the solution :)

@wBob 2012-10-18 14:03:16

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 2012-10-18 14:23:16

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.

@wBob 2012-10-19 16:29:19

Just for fun I put the two forms of data into Excel and charted each one. Guess what happened?

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Apply pivot on SQL Server table

  • 2016-08-10 11:10:09
  • user103192
  • 122 View
  • 0 Score
  • 1 Answer
  • Tags:   sql-server pivot

1 Answered Questions

Pivot information - sql table

  • 2018-11-24 22:23:31
  • Bob
  • 48 View
  • 1 Score
  • 1 Answer
  • Tags:   table pivot

1 Answered Questions

[SOLVED] PIVOT with SQL Server

1 Answered Questions

[SOLVED] Pivot with rollup in sql server

1 Answered Questions

[SOLVED] MySQL pivot query trouble sorting column by total

  • 2015-09-09 23:20:27
  • Matt Fricker
  • 4520 View
  • 3 Score
  • 1 Answer
  • Tags:   mysql pivot sorting

1 Answered Questions

[SOLVED] Need help with SQL Server PIVOT

  • 2014-04-23 02:15:09
  • jamauss
  • 1250 View
  • 3 Score
  • 1 Answer
  • Tags:   sql-server pivot

Sponsored Content