SQL Server 2008 PIVOT Table

In this article I describe how to make a pivot table.
  • 2662

PIVOT Table

PIVOT and UNPIVOT are relational operators that are used to transform a table-valued expression into another table. PIVOT rotates a table valued expression by turning the unique values from one  column in to multiple columns in the output, and aggregate function is performed where they are needed on any remaining column values that are required in output. In simple words, it simply turns the value of a specified column into column names, effectively rotating a table. The reverse operation of PIVOT is UNPIVOT. UNPIVOT operator rotates the column of table valued expression into column names.

Syntax

select <non-pivoted column>,
[first pivoted column] as <column name>,
[second pivoted column name] as <column name>,
. . . . . .
[last pivoted column] as <column name>
from
(select query that produces the data)
as TableAlias
PIVOT
(
<aggregation function>(column being aggregated)--MIN,MAX,SUM,etc
FOR
[<column that contains the values that will become column headers>]
IN ([first pivoted column], [second pivoted column].....[last pivoted column] )
AS PivotTableAlias
<optional ORDER BY clause>

Example

Creation of Table

create table ProductSales(CustomerName varchar(15), ProductName varchar(15),QuantityNeeded int)

Insertion of Data and Select table

insert into ProductSales

select 'Tim', 'Coke',5 union all

select 'Tim', 'Bread',3 union all

select 'John','Sandwich',4 union all

select 'Tim','Milk',2 union all

select'John','Bread',3 union all

select 'John','Coke',6 union all

select 'Mike','Soda',12 union all

select 'Mike', 'Coke',5 union all

select 'Mike', 'Bread',1 union all

select 'Mike','Sandwich',2 union all

select 'Tim','Milk',6

select * from ProductSales

Output:

CreateTable-sql.jpg

PIVOT Table

--Pivot Table ordered by PRODUCTNAME

SELECT ProductName, John, Mike

FROM (

SELECT CustomerName, ProductName, QuantityNeeded

FROM ProductSales) up

PIVOT (SUM(QuantityNeeded) FOR CustomerName IN (John, Mike)) AS [pivot]

ORDER BY ProductName

GO

Output:

Suppose you want to determine number of products needed by certain customers. The following query provides this report ordered by customer.

PivotTable.jpg

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.