SQL Tip: Creating a PIVOT table (Part 2)

[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:
Pivot2-1stOutput

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:
Pivot2-2ndOutput

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 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).

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s