SQL Tip: Creating a Pivot table via T-SQL


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

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:
Host
AdvertisementID
LastStateName
KART7
CAZ201F8
Succeeded
KART7
CAZ201F9
Succeeded
KART7
CAZ20204
Succeeded
KART7
CAZ2020A
Failed
KART7
CAZ2020E
Failed
KART7
CAZ20211
Succeeded
KART7
CAZ20212
Succeeded
KART7
CAZ20217
Succeeded
KART7
CAZ20219
Succeeded
KART7
CAZ2021D
Failed
 
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:
Host
LastStateName
NumberOfAdvertIDs
KART7
Failed
3
KART7
Succeeded
7
 
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:
Host
TotalAdvertisements
Succeeded
Waiting
Failed
Retrying
KART7
10
7
0
3
0

 

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.
Comments (0)

Skip to main content