[MS SQL SERVER] PIVOT

PIVOT 구문은 ROW로 출력된 결과를 COLUMN으로 변환시키는 역할을 한다.

예를 들어, 아래 쿼리는 연도별, 주문ID별, 주문수량의 합계를 출력한다.

결과를 보면 주문ID별로 2011, 2012, 2013, 2014 이렇게 4개의 ROW로 출력되어 한 눈에 확인이 어려운 느낌이다.

SELECT CONVERT(VARCHAR(4), DueDate, 112) [DueYEAR]
	, LEFT(PurchaseOrderID,1) [PurchaseOrderID]
	, SUM(OrderQty) [OrderQty]
FROM [AdventureWorks2014].[Purchasing].[PurchaseOrderDetail]
GROUP BY CONVERT(VARCHAR(4), DueDate, 112)
	, LEFT(PurchaseOrderID,1)
ORDER BY LEFT(PurchaseOrderID,1)

이 때, PIVOT 구문을 사용한다면 연도컬럼이 ROW가 아닌 COLUMN으로 변환되어 아래처럼 출력이 가능하다.

SELECT *
FROM (
	SELECT CONVERT(VARCHAR(4), DueDate, 112) [DueYEAR]
		, LEFT(PurchaseOrderID,1) [PurchaseOrderID]
		, SUM(OrderQty) [OrderQty]
	FROM [AdventureWorks2014].[Purchasing].[PurchaseOrderDetail]
	GROUP BY CONVERT(VARCHAR(4), DueDate, 112)
		, LEFT(PurchaseOrderID,1)
	) A
PIVOT (SUM(OrderQty) FOR DueYEAR IN ([2011], [2012], [2013], [2014])) AS PVT;

반대로 주문ID를 COLUMN으로 변경하는 것도 가능하다.

SELECT *
FROM (
	SELECT CONVERT(VARCHAR(4), DueDate, 112) [DueYEAR]
		, LEFT(PurchaseOrderID,1) [PurchaseOrderID]
		, SUM(OrderQty) [OrderQty]
	FROM [AdventureWorks2014].[Purchasing].[PurchaseOrderDetail]
	GROUP BY CONVERT(VARCHAR(4), DueDate, 112)
		, LEFT(PurchaseOrderID,1)
	) A
PIVOT (SUM(OrderQty) FOR PurchaseOrderID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) AS PVT;

 

반대로 PIVOT된 테이블을 UNPIVOT도 가능하다

--UNPIVOT
SELECT *
INTO #UNPIVOT
FROM (
	SELECT CONVERT(VARCHAR(4), DueDate, 112) [DueYEAR]
		, LEFT(PurchaseOrderID,1) [PurchaseOrderID]
		, SUM(OrderQty) [OrderQty]
	FROM [AdventureWorks2014].[Purchasing].[PurchaseOrderDetail]
	GROUP BY CONVERT(VARCHAR(4), DueDate, 112)
		, LEFT(PurchaseOrderID,1)
	) A
PIVOT (SUM(OrderQty) FOR DueYEAR IN ([2011], [2012], [2013], [2014])) AS PVT;


SELECT * FROM #UNPIVOT

SELECT *
FROM #UNPIVOT
UNPIVOT (OrderQty FOR DueYEAR IN ([2011], [2012], [2013], [2014])) AS UPVT;

PIVOT 구문을 사용할 때 COLUMN이 되는 값은 정해져 있지 않기 때문에 위의 예처럼 값을 지정하는 것은 실제로 사용하기가 쉽지 않다. 아래처럼 해당되는 COLUMN 값을 미리 변수에 지정하여 dynamic하게 사용이 가능하다.

 

DECLARE @COL NVARCHAR(MAX) = ''
	, @SQL NVARCHAR(MAX) = ''

SELECT @COL += QUOTENAME(CONVERT(VARCHAR(4), DueDate, 112)) + ','
FROM [AdventureWorks2014].[Purchasing].[PurchaseOrderDetail]
GROUP BY CONVERT(VARCHAR(4), DueDate, 112)

SET @COL = LEFT(@COL, LEN(@COL) - 1)
SELECT @COL

SET @SQL = '
	SELECT *
	FROM (
		SELECT CONVERT(VARCHAR(4), DueDate, 112) [DueYEAR]
			, LEFT(PurchaseOrderID,1) [PurchaseOrderID]
			, SUM(OrderQty) [OrderQty]
		FROM [AdventureWorks2014].[Purchasing].[PurchaseOrderDetail]
		GROUP BY CONVERT(VARCHAR(4), DueDate, 112)
			, LEFT(PurchaseOrderID,1)
	) A
	PIVOT (
		SUM(OrderQty) FOR DueYEAR IN (' + @COL + ')
	) AS PVT;'

EXECUTE sp_executesql @SQL;