SQL PIVOT

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)

Next: PIVOT the results

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

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

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

SELECT CompanyName, COUNT(P.Id) AS Count
  FROM Product P
  JOIN Supplier S ON P.SupplierId = S.Id
 GROUP BY CompanyName

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.

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

Only the first 6 suppliers are included for display purposes.

• Our Sql GROUP BY Tutorial
• Our Sql Aggregrates Functions Reference
• Our Sql Functions Reference