T-SQL Tutorials - TSQLTutorials.com

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

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:

CountryVariableVariableValue
North AmericaSales2000000
North AmericaExpenses1250000
North AmericaTaxes250000
North AmericaProfit500000
EuropeSales2500000
EuropeExpenses1250000
EuropeTaxes500000
EuropeProfit750000
South AmericaSales500000
South AmericaExpenses250000
AsiaSales800000
AsiaExpenses350000
AsiaTaxes100000

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 ExpensesTaxes Profit
North America20000001250000 250000 500000
Europe 25000001250000 500000 750000
South America500000 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 America20000001250000
Europe 25000001250000
South America500000 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:

RecordVariableVariableValue
11First Value
12Second Value
13Third Value
14Fourth Value
21First Value
22Second Value
23Third Value
27Seventh Value
28Eighth Value
39Ninth Value
42Second Value
45Fifth Value
410Tenth 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:

Record12345678
1First ValueSecond ValueThird ValueFourth Valuenullnullnullnull
2First ValueSecond ValueThird ValuenullnullnullSeventh ValueEighth Value
3nullnullnullnullnullnullnullnull
4nullSecond ValuenullnullFifth Valuenullnullnull


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 ExpensesTaxes Profit
North America20000001250000 250000 500000
Europe 25000001250000 500000 750000
South America500000 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 ExpensesTaxes Profit
North America20000001250000 250000 500000
Europe 25000001250000 500000 750000
South America500000 250000 null null
Asia 800000 350000 100000 null
Grand Total 5800000 3100000 850000 1250000


228-09586 Microsoft SQL Server 2012 Standard 10 CAL (Not to US Version) - Sealed

Price: $1975
Buy It Now
NEW SEALED MS SQL Server 2012 Standard SP1 - 1 Server 5 CAL License 64-Bit

Current Price: $495
Current Bids: 0
A5K-02817 Microsoft SQL Server 2008 Workgroup R2 5 CAL - Sealed

Price: $575
Buy It Now
MS SQL Server 2008 R2 Enterprise, 1 Server, with 5 CAL License. New.

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

Price: $885
Buy It Now
New Microsoft SQL Server 2012 Standard 1 Server 5 CAL 64-Bit Std

Price: $600
Buy It Now







Copyright 2014 - TSQLTutorials.com