PIVOT Statement

Introduction

The PIVOT statement is used for changing rows into columns in a SQL Query (Crosstab). The PIVOT Statement is generally written in this form:

• SELECT columns
• FROM table
• PIVOT
  (
    Aggregate Function(Measure Column)
    FOR Pivot Column IN ([Pivot Column Values])
  )
  AS Alias

Beginning Example - Example #1

Download This Script
This example will use the following table: SELECT Country, Variable, VariableValue
FROM #temp123 Here is a resultset from the above SELECT query:

Now we can use the PIVOT Keyword to create a crosstab result. This will make columns for Sales, Expenses, Taxes and Profit. A single row will be displayed for each country.

SELECT *
FROM #temp123
PIVOT
(
  SUM(VaribleValue)
  FOR [Variable] IN ([Sales],[Expenses],[Taxes],[Profit])
)
AS p
Here is a resultset from the above PIVOT query:

If for the above example, we just want the Sales and Expenses columns for each country, then we can use this query:

SELECT *
FROM #temp123
PIVOT
(
  SUM(VaribleValue)
  FOR [Variable] IN ([Sales],[Expenses])
)
AS p
Here is a resultset from the above PIVOT query:

Another Example - Example #2

Download This Script
This example will use the following table: SELECT Record, Variable, VariableValue
FROM #temp123 Here is a resultset from the above SELECT query:

Now we can use the PIVOT Keyword to create a crosstab result. This will make columns for 'Variable': 1,2,3,4,5,6,7,8. A single row will be displayed for each record.
Note: We are not making columns for 'Variable' 9 and 10.

SELECT *
FROM #temp123
PIVOT
(
  MAX(VaribleValue)
  FOR [Variable] IN ([1],[2],[3],[4],[5],[6],[7],[8])
)
AS p
Here is a resultset from the above PIVOT query:

Dynamic PIVOT - PIVOT without specifying columns

Download This Script
Normally when using the PIVOT command the developer has to specify the columns that need be created for the pivot. It is possible to create a crosstab query without specifying the values that you want to use for columns. This is very handy when you don't know all of the possible values you want to pivot. However, in SQL Server 2005 this can only be done by building the PIVOT string and then using the EXECUTE (or EXEC) function, to run the query. In this example we will use the dataset from Example #1. There are different ways to build the query, but here is one example: DECLARE @columns VARCHAR(8000)

SELECT @columns = COALESCE(@columns + ',[' + cast(Variable as varchar) + ']',
'[' + cast(Variable as varchar)+ ']')
FROM #temp123
GROUP BY Variable


DECLARE @query VARCHAR(8000)

SET @query = '
SELECT *
FROM #temp123
PIVOT
(
MAX(VaribleValue)
FOR [Variable]
IN (' + @columns + ')
)
AS p'

EXECUTE(@query)
The above script will first define a variable for storing the new column list.
The next step will select each 'Variable' field and append it to the @columns variable, it will also place brackets around each field.
The next step defines the variable for storing the query.
The fourth step builds the query string and stores it in the query variable
The last step runs the query using the EXECUTE function


Here is a resultset from the above Dynamic PIVOT query:

Dynamic PIVOT with Grand Total Row - PIVOT without specifying columns

Download This Script
This is the same as the above dynamic pivot, except it also adds a Grand Total Row to the dataset. This was created in response to a user's question.
DECLARE @columns VARCHAR(8000)
DECLARE @TotalColumns VARCHAR(8000)

SELECT
@columns = COALESCE(@columns + ',[' + cast(Variable as varchar) + ']',
'[' + cast(Variable as varchar)+ ']'),

@TotalColumns =
COALESCE(@TotalColumns + ',SUM([' + cast(Variable as varchar) + ']) as [' + cast(Variable as varchar) + ']',
'SUM([' + cast(Variable as varchar)+ ']) as [' + cast(Variable as varchar)+ ']')

FROM #temp123
GROUP BY Variable

DECLARE @query VARCHAR(8000)
SET @query = '
SELECT *
FROM #temp123
PIVOT
(
MAX(VaribleValue)
FOR [Variable]
IN (' + @columns + ')
)
AS p
UNION
SELECT ''Grand Total'',' + @TotalColumns + '
FROM #temp123
PIVOT
(
MAX(VaribleValue)
FOR [Variable]
IN (' + @columns + ')
)
AS total
'

EXECUTE(@query)

The above script will first define two variables for storing the new column list and the Total select columns.
The next step will select each 'Variable' field and append it to the @columns variable, it will also place brackets around each field.
It will also select each 'Variable' field and append it to the @TotalColumns variable with the SUM() function around it as well as specifying the name of the field.
The next step defines the variable for storing the query.
The fourth step builds the query string and stores it in the query variable
The last step runs the query using the EXECUTE function

Here is a resultset from the above Dynamic PIVOT query:

MICROSOFT SQL SERVER 2012 BUSINESS INTELLIGENCE 25 CAL RETAIL BOX D2M-00063 NEW! Price: $6499 Buy It Now

★ Microsoft SQL Server 2005 Standard Edition 5 CAL ★ Dell - Sealed NIB Current Price: $100 Current Bids: 1

228-00690 Microsoft SQL Server 2000 Standard 5 CAL Price: $287.13 Buy It Now

228-04023 Microsoft SQL Server 2005 Standard 5 CAL - Open Price: $1085 Buy It Now

Microsoft SQL Server 2000 Standard 5 CAL - Brand New Sealed 228-00683 Price: $199.99 Buy It Now

Microsoft SQL Server 2000 Enterprise 25 CAL's Price: $584.99 Buy It Now