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