Using SQL pivots for SCCM data queries
This is probably elementary for the SQL gurus out there but I learned something new yesterday and thought I would share it. Have you ever had multiple row entries that you wanted to roll up into a columnar format? This situation arose the other day when a poster on the myITforum mailing list was trying to take multiple rows from the view v_AgentDiscoveries to report on SCCM client discovery metrics. By default, the data is listed like this:
This is great - all of our data is there. But what if we want a single line for each ResourceID? Pivot tables to the rescue - the SQL version that is. Here is a screen grab of the query (I have also attached the code to this post).
So there you have it - the PIVOT function! I was always a bit foggy on pivot tables in Excel but it was nice to find this functionality built into SQL. Not that the online documentation states that the PIVOT function is only available in SQL 2005 and above so as always, please test test test!
Thanks and happy querying.