SQL Server : PIVOT Timestamp and calculate

Question

I have the following table

+--------+-------------+-------------+-------------------------+-----------------+
| Server | DriveLetter | SampleValue |        Timestamp        |   CounterName   |
+--------+-------------+-------------+-------------------------+-----------------+
| srv1   | C:          |        3357 | 2014-05-23 12:15:37.000 | Free Megabytes  |
| srv1   | C:          |       25000 | 2014-05-23 11:49:19.963 | Total Disk Size |
| srv1   | D:          |       87183 | 2014-05-23 12:45:37.000 | Free Megabytes  |
| srv1   | D:          |      165725 | 2014-05-23 11:49:19.963 | Total Disk Size |
| srv2   | C:          |       61351 | 2014-05-23 12:17:17.000 | Free Megabytes  |
| srv2   | C:          |      104900 | 2014-05-23 11:07:21.643 | Total Disk Size |
| srv2   | F:          |      150918 | 2014-05-23 12:17:17.000 | Free Megabytes  |
| srv2   | F:          |      370880 | 2014-05-23 11:07:21.643 | Total Disk Size |
+--------+-------------+-------------+-------------------------+-----------------+

I now need to pivot this table into the following

+--------+-------------+----------------+----------------------------+-----------------+-----------------------------+---------------------+
| Server | DriveLetter | Free Megabytes | Timestamp (Free Megabytes) | Total Disk Size | Timestamp (Total Disk Size) | % Free (Calculated) |
+--------+-------------+----------------+----------------------------+-----------------+-----------------------------+---------------------+
| srv1   | C:          |           3357 | 2014-05-23 12:15:37.000    |           25000 | 2014-05-23 11:49:19.963     | 13,428              |
| srv1   | D:          |          87183 | 2014-05-23 12:45:37.000    |          165725 | 2014-05-23 11:49:19.963     | 52.607              |
| srv2   | C:          |          61351 | 2014-05-23 12:17:17.000    |          104900 | 2014-05-23 11:07:21.643     | 58.485              |
| srv2   | F:          |         150918 | 2014-05-23 12:17:17.000    |          370880 | 2014-05-23 11:07:21.643     | 40.691              |
+--------+-------------+----------------+----------------------------+-----------------+-----------------------------+---------------------+

I created a PIVOT Query but now I got stuck while trying to attach the Timestamp to the value. I also have no clue how to force a calculation into a new column.

SELECT        
   Server, DriveLetter, [Free Megabytes], [Total Disk Size]
FROM          
   (SELECT 
       Server, DriveLetter, CounterName, SampleValue
    FROM 
       dbo.DiskInfo_LastCheck) AS d
PIVOT (MAX(SampleValue) FOR CounterName in ([Free Megabytes], [Total Disk Size])) as P
ORDER BY Server

Sorry I'm a total SQL newbie...

Since you need to pivot 2 columns you'll first need to look at unpivoting the `value` and `timestamp` columns first, then apply pivot. Here are a few question/answers that should help http://stackoverflow.com/questions/21554504/convert-multiple-columns-row-values-into-multiple-columns, http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates — Taryn, May 23 '14 at 13:59

score 1 · Accepted Answer · answered May 23 '14 at 14:20

I would suggest doing this with conditional aggregation rather than pivot:

select lc.server, lc.DriveLetter
       max(case when CounterName = 'Free Megabytes' then SampleValue end) as [Free Megabytes],
       max(case when CounterName = 'Free Megabytes' then TimeStamp end) as [TimeStamp Free Megabytes],
       max(case when CounterName = 'Total Disk Size' then SampleValue end) as [Total Disk]
       max(case when CounterName = 'Total Disk Size' then TimeStamp end) as [TimeStamp Total Disk],
       (100 * max(case when CounterName = 'Free Megabytes' then cast(SampleValue as float) end) /
        max(case when CounterName = 'Total Disk Size' then SampleValue end)
       ) as [Percent Free]
from dbo.DiskInfo_LastCheck lc
group by lc.server, lc.DriveLetter;

SQL Server : PIVOT Timestamp and calculate

1 Answers1