Pivots Turn Ugly to Pretty

Here's a cool way to look at data, formatted using simple T-SQL PIVOT syntax.

You will often find that data stored in rows in normalized tables isn't in the prettiest form to display to an end user. Often, data will make more of an impact if it is displayed as a single row for each object with multiple columns of data. If you are looking at sales for specific customers, it is more pleasing to see one row for each customer and a column for each year of data. It also makes it easier to compare one client to another for a particular year.

Getting the data in a nice format means turning to some PIVOT syntax. Let's look at an example of using PIVOT to get data out rows and into columns. Take a look at the following sales table (some rows omitted):

SELECT * FROM DBO.SALES

customerid   year       sales                 Qtr
----------- ----------- --------------------- -----------
1           2007        25000.00              1
1           2008        22000.00              1
1           2007        34555.00              2
2           2007        35500.00              1
2           2008        56800.00              1
1           2007        784747.00             3
3           2007        15600.00              1
3           2008        95600.00              1
...         ...         ...                   ...

This table contain one row of sales data for each quarter for each customer. This is a little hard to look through and we only have three customers in this sample table. We can use the PIVOT syntax to make this data more clear. First I will show you the code and then we will look at how it works. Running the following SELECT with a PIVOT returns the data shown:

SELECT * FROM
(SELECT customerid, year, sales from dbo.SALES) AS SourceTable
PIVOT
(
SUM(sales)
FOR year IN ([2007], [2008])
  ) AS PivotTable

customerid  2007                  2008
----------- --------------------- ---------------------
1           867746.00             52348687.00
2           483332975.00          35290565548.00
3           85647195.00           840055799.00

This query provides a nice, clear table with one row for each client and a column with the sales total for that year.

Let's look at the syntax and see how this is defined. The meat of this query is the two derived tables; in this example they are called SourceTable and PivotTable. The first SELECT defines your raw data that will be fed into the PIVOT. This is just the SELECT statement we looked at earlier returning the columns we want to see. The source data is followed by the PIVOT portion of the code. Here, we define that we want to see a SUM of the sales data for the year column where the year value is 2007 or 2008. The result is the table shown above.

By no means is this the only way to pivot data -- you'll find other methods in SSIS, third-party applications and even T-SQL itself. The example I show here happens to be a handy little syntax and since you may encounter code or situations that call for it, it is good to know how PIVOT works. You can even build more complex pivots using the PIVOT operator. So go forth and play with this a little; happy coding!

About the Author

Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology consulting. He is also the President of the Colorado Springs SQL Server User Group. He can be contacted at www.consortioservices.com.

Featured

  • How To Fix the Hyper-V Read Only Disk Problem

    DOS might seem like a relic now, but sometimes it's the only way to fix a problem that Windows seems ill-equipped to deal with -- like this one.

  • Microsoft Warns IT Pros on Windows Netlogon Fix Coming Next Month

    Microsoft on Thursday issued a reminder to organizations to ensure that their systems are properly patched for a "Critical"-rated Windows Netlogon vulnerability before next month's "update Tuesday" patch distribution arrives.

  • Microsoft Nudging Skype for Business Users to Teams

    Microsoft on Thursday announced some perks and prods for Skype for Business unified communications users, with the aim of moving them to the Microsoft Teams collaboration service instead.

  • How To Improve Windows 10's Sound and Video Quality

    Windows 10 comes with built-in tools that can help users get the most out of their sound and video hardware.

comments powered by Disqus