SQL Pivot Tables

timothycope T-SQL

Background

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

A Note on Performance

Performing a PIVOT on a large table takes a lot of resources. If at all possible, SELECT your data into a temp table or table variable. For data sets containing more than a few hundred rows, use a temp table. Temp tables (as opposed to table variables) allow indexing, which can help your PIVOT or other aggregations run faster.

Sample Data

In the following examples this table will be referred to as #Temp.

07072014_1

Sample Pivot

SELECT [Affiliate], [January], [February], [March]
FROM (SELECT [Affiliate], [Month], [Sales] FROM #Temp) AS T
PIVOT(SUM([Sales]) FOR [Month] IN ([January], [February], [March])) AS PVT

Sample Result Set

07072014_2

Dissecting the Statement

SELECT [Affiliate], [January], [February], [March]

  • These are the names of the columns for the Result Set
  • Plain English: Show columns named…

FROM (SELECT [Affiliate], [Month], [Sales] FROM #Temp) AS T

  • The data we want to pull in to pivot
  • Notice [Month], which contains the values ‘January’, ‘February’, and ‘March’
  • Plain English: Pull data from this table

PIVOT(SUM([Sales]) FOR [Month] IN ([January], [February], [March])) AS PVT

  • An aggregate function is required
    • With this example, we want to add up the [Sales] by [Month]
  • FOR is used to find the [Sales] as they relate to [Month]
  • IN is used to find the values to group the aggregation (SUM in this case)
  • Plain English: Group [sales] by [month], where the month is a given value

Helpful Tips

  • Use isNull() to replace NULL with a value
    • Syntax: isNull([ColumnName], {return value if null}) AS ‘ColumnName’
    • Example: isNull([Sales], 0) AS ‘Sales’
      • Returns a 0 if [Sales] is NULL
      • Don’t forget to name your column or it will be returned as “(no column name)”
    • Usage: Use for values that are displayed in the Result Set
      • Simple Select: SELECT [CustomerID] , isNull([CommAmt], 0) FROM …
      • Aggregated Select: GROUP BY isNull([CommAmt], 0)
      • PIVOT Select: SELECT [Affiliate], isNull([January], 0) AS ‘January’, …
    • Data Type: The replacement value must be the same data type as the column
      • Using the above examples, we surmise that [CommAmt] is DECIMAL
      • We must return a DECIMAL value (a number)
      • We cannot return ‘No Sales’ as that would be a string (NVARCHAR)
  • Use MONTH() to extract the month from a date
    • The sample table was created using a case statement as MONTH([Date]) returns a value 0-12 as part of the SELECT statement:
    • ,CASE MONTH([Date])
WHEN 1 THEN ‘January’
WHEN 2 THEN ‘February’
WHEN 3 THEN ‘March’
END AS ‘Month’