[edit: April 2019] This was originally posted to my internal (to Microsoft) blog sometime in 2011/2012 as part of a series I called ‘SQL Tips’ for coworkers and an email distribution list people could sign up to. I have left this as originally written but have updated formatting for my WordPress theme.
Last tip we reviewed how to create a pivot table using aggregate functions. I was asked by a few people if the PIVOT function could pivot data in a more dynamic way than what I showed. The simple answer is: No. However, I will show how to use the PIVOT function in this tip; this will do two things. One, you will know another way to pivot data and you can choose the method you prefer and two, it will show why it can’t dynamically pivot data (meaning, pivot the data even if you don’t know all the values and define them explicitly in your query).
We’ll use the same query we started with last time:
The initial query (or problem):
SELECT sis.Netbios_Name0 AS [Host] ,cas.AdvertisementID ,cas.LastStateName FROM dbo.v_ClientAdvertisementStatus cas INNER JOIN dbo.v_R_System sis ON sis.ResourceID = cas.ResourceID INNER JOIN dbo.ProgramOffers adv ON adv.OfferID = cas.AdvertisementID WHERE adv.PresentTime >= DATEADD(day,-30,GETDATE()) AND adv.MandatorySched != N'' AND sis.Netbios_Name0 = N'KART7'; GO
And the output:
The PIVOT Function:
Let’s look at the makeup of the PIVOT function; I’ve put together a skeleton query but will highlight the PIVOT function.
SELECT [All fields you want to return] FROM (-- This is a subquery --SELECT [the fields from original query (usually)] FROM [tables and joins here] etc ) dta PIVOT -- The PIVOT Keyword; this is where the pivotting information will be specified (AGGREGATE_FUNCTION([Column to Aggregate]) -- i.e. COUNT, SUM, AVG, etc FOR [Column that will be turned into the column headers] IN ([pivoted column 1],[pivoted column n]) -- The names of the pivoted columns to create ) pvt
So, using this structure and the query from ‘the problem’ we’ll create the same output as we created with just the aggregates.
SELECT Host ,Succeeded ,Waiting ,Failed ,Retrying FROM ( SELECT sis.Netbios_Name0 AS [Host] ,cas.AdvertisementID ,cas.LastStateName FROM dbo.v_ClientAdvertisementStatus cas INNER JOIN dbo.v_R_System sis ON sis.ResourceID = cas.ResourceID INNER JOIN dbo.ProgramOffers adv ON adv.OfferID = cas.AdvertisementID WHERE adv.PresentTime >= DATEADD(day,-30,GETDATE()) AND adv.MandatorySched != N'' AND sis.Netbios_Name0 = N'KART7' ) dta PIVOT (COUNT(AdvertisementID) FOR LastStateName IN ([Succeeded],[Waiting],[Failed],[Retrying]) ) pvt; GO
And the output:
You’ll notice that you still have to manually define the values in the “IN” statement and then in the main “SELECT”. Using our data…
PIVOT (COUNT(AdvertisementID) -- you have to define what to aggregate and how FOR LastStateName -- This is where you define what column will be pivotted as the column headers IN ([Succeeded],[Waiting],[Failed],[Retrying]) -- lastly, you must define the different values aka columns which you can choose from.
For those who really want to create the pivot dynamically…the easiest way is to use the PIVOT function along with “dynamic SQL”. Basically, your query essentially builds the final query to run and, assuming you have the correct you have the rights to do so, execute the query that was dynamically written. I recently created an example for someone in the team:
-- Dynamically Pivot a query SET NOCOUNT ON; DECLARE @Select nvarchar(4000) ,@Columns nvarchar(max); -- Get some sample data and store into a temp table; -- We'll be looking at the results of the "LastError" field: SELECT sis.Netbios_Name0 AS [Host] ,sts.LastStatusMessageIDName AS [LastError] INTO #Data FROM dbo.v_ClientAdvertisementStatus sts INNER JOIN dbo.v_R_System sis ON sis.ResourceID = sts.ResourceID WHERE sts.LastStateName = N'Failed' AND sis.Netbios_Name0 IN (N'Computer1', N'Computer2', N'Computer3'); -- Create a list of "columns" from the distinct values in "LastError": SELECT @Columns = COALESCE(@Columns + ', ', '') + QUOTENAME(LastError) FROM (SELECT DISTINCT LastError FROM #Data WHERE LastError IS NOT NULL) a; -- Create the dynamic SQL to be executed: SET @Select = N'SELECT Host, '+@Columns+N' FROM #Data d PIVOT (COUNT(LastError) FOR LastError IN ('+@Columns+')) pvt;'; ---- Uncomment this if you don't have the rights to EXECUTE and just copy/paste this select into the window. ---- Make sure you comment out the drop table first though as you won't be able to run the query if you drop the temp table first! --SELECT @Select; ---- This is where it may not work for you...If so, just comment out/delete the following 2 lines and follow instructions above EXECUTE sp_executesql @Select; DROP TABLE #Data; GO