MSSQL PIVOT AND UNPIVOT

 

SQL 서버 2000에서 관계형 데이터에 행별로 저장된 값을, 가로 테이블로 된 형식으로 보기 위해서는 CASE문을 써야만 그렇게 볼 수 있었다. 하지만 SQL 서버 2005에서는 PIVOT 연산자를 이용하여 간단히 구현할 수 있다. 

한 예로 연도별 판매사원의 매출을 구하는 예제를 보도록 하자. 

다음은 SQL 서버 2000 방식으로 구현한 예제다.

[코드1]

SELECT SalesPersonID
 ,SUM( case Year(OrderDate) when 2002 then TotalDue else 0 end ) as [2002]
 ,SUM( case Year(OrderDate) when 2003 then TotalDue else 0 end ) as [2003]
 ,SUM( case Year(OrderDate) when 2004 then TotalDue else 0 end ) as [2004]
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID;


SalesPersonID 2002                  2003                  2004
------------- --------------------- --------------------- ---------------------
278           1604754.5514          1851628.4003          755593.2997
281           2973850.1213          3177895.6297          1429353.8926
275           4137233.9019          5244417.2148          2053782.7569
...

이번에는 2005 방식으로 구현한 예이다.

WITH C ( SalesPersonID, TheYear, TotalDue)
AS
(
 SELECT SalesPersonID
   , Year( OrderDate) AS TheYear
   , TotalDue
 FROM Sales.SalesOrderHeader
 
 
)

SELECT SalesPersonID , [2002],[2003],[2004]
FROM C
PIVOT ( SUM(TotalDue) FOR TheYear IN ( [2002],[2003],[2004]) ) AS PVT;



SalesPersonID 2002                  2003                  2004
------------- --------------------- --------------------- ---------------------
NULL          7216029.7246          10819121.9238         10796844.5288
268           530374.4999           610881.0169           333855.4924
275           4137233.9019          5244417.2148          2053782.7569

[코드2]

다음 예에서는 공급업체 ID별로 분류된 직원 ID 164, 198, 223, 231 및 233에 의해 작성된 구매 주문 번호를 반환합니다.

USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM (SELECT PurchaseOrderID, EmployeeID, VendorID
      FROM Purchasing.PurchaseOrderHeader) p
PIVOT (COUNT (PurchaseOrderID) FOR EmployeeID IN ( [164], [198], [223], [231], [233] )) AS pvt
ORDER BY VendorID;

다음은 결과 집합의 일부입니다.

====================================================================

VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
1           4           3           5           4           4
2           4           1           5           5           5
3           4           3           5           4           4
4           4           2           5           5           4
5           5           1           5           5           5
====================================================================
--------------------------------------------------------------------
[코드3]
테이블의 피벗을 해제하기 위해 앞의 예에서 생성된 결과 집합이 pvt에 저장된다고 가정합니다. 
쿼리는 다음과 같습니다.
--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int, Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
      FROM pvt) p
UNPIVOT(Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5))AS unpvt
GO
다음은 결과 집합의 일부입니다.
====================================================================
VendorID    Employee    Orders
1           Emp1        4
1           Emp2        3
1           Emp3        5
1           Emp4        4
1           Emp5        4
2           Emp1        4
2           Emp2        1
2           Emp3        5
2           Emp4        5
2           Emp5        5
====================================================================
[출처] [MS-SQL2005] PIVOT 및 UNPIVOT 사용|작성자 하얀방울