Sit ‘n Spin: Pivot in SQL Server

I have been playing around with WinAudit for the past week.  Overall it is an awesome little package.  One of it’s major selling points is the fact you can stuff the audit results into a database.  What more could an admin want?

The only drawback is the schema of the collected data.  In order to be be “futureproof” the data is stored into a single table.  The data is stored with a “Category” and “ItemName” with up to 5 values.  For example, the version of a software package would have a Category of “Installed Software”, an ItemName of “Adobe Flash” and an ItemValue1 of “10.0.45.2”.  The type of the item values is implied and not really specified anywhere.

This is great for extensibility since you can add new lines with reckless abandon.  Need to add some data about the CPU  temperature, no problem!  Simply add a “Category” of Hardware, an “ItemName” of CPU Temp and set the value to the temperature.  The problem is that it is pants for generating reports or summary type operations.

What you want is another table that contains the data as columns.  The picture to the left shows what I mean.

This problem is not insurmountable.  We could take the long, hard road and select the values one by one based on the Category and ItemName, inserting them into another table with columns based on the selected data point.  This is a pain and fraught with danger.  There has to be a better way.

The better way is using the Pivot command in SQL.  As shown below, it is all done in one command.  Unfortunately it isn’t crystal clear what is going on.  Let’s take a look at some key parts…

  1: INSERT INTO [IT].[dbo].[ComputerInfo]
  2:            ([ComputerName]  ,[DomainName]
  3:            ,[Description]   ,[OS]
  4:            ,[Manufacturer]  ,[Model]
  5:            ,[SerialNumber]  ,[AssetTag]
  6:            ,[Processors]    ,[ProcessorDescription]
  7:            ,[Memory]        ,[HardDrive]
  8:            ,[BIOS]          ,[UpTime]
  9:            ,[Roles])
 10: Select max([Computer Name]) As ComputerName,
 11:        max([Domain Name]) as DomainName, max([Description]) as Description,
 12:        max([Operating System]) as OperatingSystem, 
 13:        max([Manufacturer]) as Manufacturer, max([Model]) as Model,
 14:        max([Serial Number]) as SerialNumber, max([Asset Tag]) as AssetTag,
 15:        max([Number Of Processors]) as NumProcs, 
 16:        max([Processor Description]) as ProcDescription,
 17:        max([Total Memory]) as TotalMemory,
 18:        max([Total Hard Drive]) as TotalHDD, 
 19:        max([BIOS Version]) as BIOS, max([System Uptime]),
 20:        max([Roles]) As Roles
 21:   From [IT].[dbo].[WinAudi2] as data
 22: pivot (max(ItemValue1) for ItemName in 
 23:       ([Computer Name], [Domain Name], [Description],
 24:        [Roles], [Operating System], [Manufacturer], [Model],
 25:        [Serial Number], [Asset Tag], [Number Of Processors],
 26:        [Processor Description], [Total Memory], [Total Hard Drive], [System Uptime], [BIOS Version]
 27:       )) as result
 28:    where Category = 'System Overview'
 29:    group by Computer

This is an overly complex example as I needed to combine several different audit records for the same PC.  The basic structure of the pivot command is as follows…

  1: Select <New Columns> 
  2:   From <Original Table> as TableTag
  3:  Pivot (AggrergateCommand(Column to use as value) 
  4:            for OtherColumn in
  5:              ([Value that OtherColumn is equal to],
  6:               [Next value that OtherColumn is equal to],
  7:               ...)
  8: 

Pivoting is done on a single column.  In our case, it is the ItemName column.  When ItemName is “Computer Name” we want the value in the ItemValue1 column to be the value associated with the ComputerName column.  Makes sense.

I was confuse by the [Name] notation at first.  This is basically a faux variable that is also used as a comparison value.  Basically the internal phrase is used as ItemName = ‘Operating System’ for example. 

The problem is that many rows might have ItemName = ‘Operating System’.  That is why we have to have some sort of aggregation function.  I chose Max() since it is simple and the values should be equal anyway.