PIVOT SQL Server Example

SQL Server  > Operators > PIVOT

PIVOT rotates a values from a table by turning the unique values from one column into multiple columns in the output (cross-tab). Also performs aggregations where they are required.

Example:

1.Using PIVOT to get number of product sales

create table #Sales(ProductId int, [Year] int, Country varchar(100))

insert into #Sales(ProductId, [Year], Country  ) values  (1,2012,'Germany'), (1,2012,'UK') , (1,2013,'France') , (2,2013,'Germany')  , (2,2013,'Germany')  , (2,2013,'UK')

SELECT

       Country, [2012], [2013]
FROM
       (SELECT ProductId, [Year], Country FROM #Sales) p
       PIVOT
       (
              COUNT (ProductId)
              FOR [Year] IN ( [2012], [2013] )
       ) AS pvt
ORDER BY pvt.Country ;

drop table #Sales

Result:



2. Dynamic PIVOT and cross-tab query