Pivots Turn Ugly to Pretty

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

• By Eric Johnson
• 03/15/2010

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!