SQL Server - 피벗 오퍼레이터 (PIVOT Operator)

피벗은 테이블을 효과적으로 순환하여 한 열의 고유값을 여러열로 변환하는데 사용할 수 있는 SQL server operator이다.

 

 

Pivot is a sql server operator that can be used to turn unique values from one column, into multiple columns in the output, there by effectively rotating a table.

 

 

Example,

Select SalesAgent, Korea, US, UK 
from 
(
	Select SalesAgent, SalesCountry, SalesAmount
    from tblProductSale
) as SourceTable
PIVOT
(
	SUM(SalesAmount) FOR SalesCountry IN([Korea], [US], [UK])
)
AS PivotTable

 

Syntax from MSDN

SELECT <non-pivoted column>,
	[first pivoted column] AS <column name>,
    [secount pivoted column] AS <column name>,
    
    ...
    
    [last pivoted column] AS <column name>
 
FROM
	(<SELECT query that producess the data>)
    AS <alias for the source query>
PIVOT
(
	<aggregation function>(<column being aggregated>)
FOR
	[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column], ... [last pivoted column])
)
AS <alias for the pivot table
<optional ORDER BY clause>