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

Note: You must use brackets around each of the Pivot Column Values

Beginning Example – Example #1

 

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

Country Variable VariableValue
North America Sales 2000000
North America Expenses 1250000
North America Taxes 250000
North America Profit 500000
Europe Sales 2500000
Europe Expenses 1250000
Europe Taxes 500000
Europe Profit 750000
South America Sales 500000
South America Expenses 250000
Asia Sales 800000
Asia Expenses 350000
Asia Taxes 100000

 

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:

Country Sales Expenses Taxes Profit
North America 2000000 1250000 250000 500000
Europe 2500000 1250000 500000 750000
South America 500000 250000 null null
Asia 800000 350000 100000 null

 

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:

Country Sales Expenses
North America 2000000 1250000
Europe 2500000 1250000
South America 500000 250000
Asia 800000 350000

 

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:

Record Variable VariableValue
1 1 First Value
1 2 Second Value
1 3 Third Value
1 4 Fourth Value
2 1 First Value
2 2 Second Value
2 3 Third Value
2 7 Seventh Value
2 8 Eighth Value
3 9 Ninth Value
4 2 Second Value
4 5 Fifth Value
4 10 Tenth Value

 

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:

Record 1 2 3 4 5 6 7 8
1 First Value Second Value Third Value Fourth Value null null null null
2 First Value Second Value Third Value null null null Seventh Value Eighth Value
3 null null null null null null null null
4 null Second Value null null Fifth Value null null null

 

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:

Country Sales Expenses Taxes Profit
North America 2000000 1250000 250000 500000
Europe 2500000 1250000 500000 750000
South America 500000 250000 null null
Asia 800000 350000 100000 null

 

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:

Country Sales Expenses Taxes Profit
North America 2000000 1250000 250000 500000
Europe 2500000 1250000 500000 750000
South America 500000 250000 null null
Asia 800000 350000 100000 null
Grand Total 5800000 3100000 850000 1250000

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s