SQL Tip: Creating a PIVOT table (Part 2)


This was originally created in 2011/2012 in a series I dubbed "SQL Tips" for my team (via email and an internal blog site). I've decided to add these SQL Tips to my external blog. These are being posted exactly how they were written ~6 years ago...unless I found someone's name being called out...I may have edited that to protect the innocent. 🙂

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:
Host
AdvertisementID
LastStateName
KART7
CAZ2020A
Failed
KART7
CAZ2020E
Failed
KART7
CAZ20211
Succeeded
KART7
CAZ20212
Succeeded
KART7
CAZ20217
Succeeded
KART7
CAZ20219
Succeeded
KART7
CAZ2021D
Failed
 
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:
Host
Succeeded
Waiting
Failed
Retrying
KART7
4
0
3
0
 
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.
 
 
Bonus Tip!:
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 <name changed to protect the innocent> 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
As you can see from the screenshot of the results of this query, the column names were dynamically created from the results of the initial query (where the data was stored in a temp table).

 

Comments (0)

Skip to main content