SQL PIVOT

The PIVOT relational operator converts rows into columns.

Pivoting offers a way to summarize large data sets with aggregations if needed.

PIVOT is mostly used for generating reports that are easier to read.

Example

#

To see how PIVOT works, we'll start with a GROUP BY operation.
This example lists the total number of orders by year.

SELECT YEAR(OrderDate) AS Year, COUNT(Id) AS Count
  FROM [Order]
 GROUP BY YEAR(OrderDate)
 ORDER BY YEAR(OrderDate)
Result:  3 records
Year Count
2012 152
2013 408
2014 270

Next: PIVOT the results

Next, use the PIVOT operator to rotate the above results: columns become rows and rows become columns.

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
SELECT '# Orders' AS Year, [2012], [2013], [2014]
  FROM (SELECT Id, DATEPART(year, OrderDate) AS Year 
          FROM [Order]) AS t
  PIVOT (
    COUNT(Id) 
    FOR Year IN (
      [2012], [2013], [2014]
    )
  ) AS PivotTable
Result:  1 record
Year 2012 2013 2014
#Orders 152 408 270

Syntax

#

Syntax for PIVOT.

SELECT first_column AS alias,
       pivot_value1, pivot_value2, ... pivot_value_n
  FROM source_table AS table_alias
 PIVOT 
 (
   aggregate_function
   FOR pivot_column
    IN (pivot_value1, pivot_value2, ... pivot_value_n)
 ) AS pivot_table_alias

first_column -- a column or expression that is the first column in the pivot table

pivot_values -- a comma-separated list of values to pivot on

source_table -- a table or SELECT statement with the source data for the pivot table

aggregate_function -- an aggregate function like SUM, COUNT, MIN, MAX, or AVG.

pivot_column -- the column with the the pivot values

pivot_table_alias -- an alias name for the output pivot table


More Examples

PIVOT

Again, we start with a GROUP BY statement.
This example lists suppliers and their product counts.
SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
SELECT CompanyName, COUNT(P.Id) AS Count
  FROM Product P
  JOIN Supplier S ON P.SupplierId = S.Id
 GROUP BY CompanyName

Result:  19 records
CompanyName Count
Aux joyeux ecclésiastiques 2
Bigfoot Breweries 3
Cooperativa de Quesos 'Las Cabras' 2
Escargots Nouveaux 1
Exotic Liquids 3
Forêts d'érables 2
Formaggi Fortini s.r.l. 3

Next: PIVOT the results

Now we use PIVOT to rotate the above results and summarize the number of products by supplier.

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
SELECT '# Products' AS 'Supplier', * FROM (
  SELECT CompanyName
    FROM Product P
   INNER JOIN Supplier S ON P.SupplierId = S.Id
   GROUP BY P.Id, CompanyName
) t 
PIVOT (
  COUNT(CompanyName) 
  FOR CompanyName IN (
    [Aux joyeux ecclésiastiques], 
    [Bigfoot Breweries], 
    [Cooperativa de Quesos 'Las Cabras'], 
    [Escargots Nouveaux], 
    [Exotic Liquids], 
    [Forêts d'érables]
  )
) AS PivotTable
Result:  1 record
Supplier Aux joyeux ecclésiastiques Bigfoot Breweries Cooperativa de Quesos 'Las Cabras' Escargots Nouveaux Exotic Liquids Forêts d'érables
# Products 2 3 2 1 3 2

Only the first 6 suppliers are included for display purposes.


You may also like



Guides


vsn 3.1