SQL Tip: Creating a Pivot table via T-SQL

[edit: April 2019] This was originally posted to my internal (to Microsoft) blog sometime in 2011 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.

Sometimes your queries will return data in a format that is less than what you’ve desired. You see the data and think to yourself, “it’s great that I have this data but I’d really like to have more of a pivot table look”. Next time you think that to yourself you’ll have a solution you can use, just keep reading.
Let’s take a query that was sent to me recently as an example. I’ve modified it a little and formatted it in a way that I like but the output is the same. I’ll add a machine name to the WHERE clause just for our examples (thanks Karthik!).

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:

Upon seeing this you may think, “You know, I really just want to know the number of ‘AdvertisementIDs’ in each ‘LastStateName’. So you update the query to do this:

SELECT  sis.Netbios_Name0 AS [Host]
       ,cas.LastStateName
       ,COUNT(cas.AdvertisementID) AS [NumberOfAdvertIDs]
 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'
 GROUP BY  sis.Netbios_Name0
          ,cas.LastStateName;
GO

And the output:

This is a fairly common type of query. But, you decide you want to pivot the data so that you have one record per ‘Host’. We shall learn how to do that next. However, before we go on it is important to stress that our solution does presume that we know all the possible states or at least the ones we wish to see/pivot on. Creating a pivot table from data where you don’t know all the possible states (the column labels) is best done using Excel or SSRS because to do so in T-SQL would require creating dynamic SQL and therefore more work than it is probably worth. Now that we’ve given the proper disclaimer, let’s learn how to create our pivot table.

Obviously, since we want to pivot the data on the ‘LastStateName’ we can no longer include it in the GROUP BY. This means it must be included in an aggregate function. If we are to do this we’ll need to separate the values using the CASE function. We’ll also need to define each column explicitly. I’ve added a total as well as the “ISNULL” function to ensure we have a value rather than a NULL.

The Solution

SELECT  sis.Netbios_Name0 AS [Host]
       ,COUNT(cas.AdvertisementID) AS [TotalAdvertisements]
       ,ISNULL(SUM(CASE cas.LastStateName WHEN N'Succeeded' THEN 1 END),0) AS [Succeeded]
       ,ISNULL(SUM(CASE cas.LastStateName WHEN N'Waiting' THEN 1 END),0) AS [Waiting]
       ,ISNULL(SUM(CASE cas.LastStateName WHEN N'Failed' THEN 1 END),0) AS [Failed]
       ,ISNULL(SUM(CASE cas.LastStateName WHEN N'Retrying' THEN 1 END),0) AS [Retrying]
 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'
 GROUP BY sis.Netbios_Name0;
GO

And the output:

A closer look at the solution:

Let’s take a look at one of the ‘columns’ (without the ISNULL).

SUM(CASE cas.LastStateName WHEN 'Succeeded' THEN 1 END)

• The CASE statement simply says to return the value of 1 if the LastStateName is ‘Succeeded’. If it doesn’t then a NULL will exist. Which, if you recall from the SQL Tip COUNTing NULL values, a NULL is not COUNTed or SUMmed.
• The SUM is how we will aggregate the column so that only one row remains for the ‘Host’.

So, there you have it; a way to pivot your data using T-SQL.