PIVOT 및 UNPIVOT 사용
PIVOT 및 UNPIVOT 관계 연산자를 사용하여 테이블 반환 식을 다른 테이블로 변경할 수 있습니다. PIVOT은 식의 한 열에 포함된 여러 고유 값을 출력에서 여러 열로 변환하여 테이블 반환 식을 회전하고 최종 출력에서 남은 열 값 중 원하는 값에 대해 필요에 따라 집계를 수행합니다. UNPIVOT은 테이블 반환 식의 열을 열 값으로 회전하여 PIVOT과 반대되는 연산을 수행합니다.
[!참고]
SQL Server 2005 이상으로 업그레이드되는 데이터베이스에 대해 PIVOT 및 UNPIVOT을 사용할 때 데이터베이스의 호환성 수준은 90 이상으로 설정해야 합니다. 데이터베이스 호환성 수준을 설정하는 방법은 sp_dbcmptlevel(Transact-SQL)을 참조하십시오.
PIVOT에 대한 구문은 복잡한 일련의 SELECT...CASE 문에서 지정할 수 있는 구문과 달리 단순하고 읽기 쉬운 구문을 제공합니다. PIVOT 구문에 대한 자세한 내용은 FROM(Transact-SQL)을 참조하십시오.
다음은 주석이 추가된 PIVOT 구문입니다.
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces 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>;
기본 PIVOT 예
다음 코드 예제에서는 4개의 행이 있는 2열 테이블을 생성합니다.
USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
결과 집합은 다음과 같습니다.
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
3일 DaysToManufacture에 대해 정의된 제품은 없습니다.
다음 코드는 같은 결과를 표시하지만 DaysToManufacture 값이 열 제목이 되도록 피벗됩니다. 또한 결과가 NULL이라도 [3]일에 대한 열을 제공합니다.
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
결과 집합은 다음과 같습니다.
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
복잡한 PIVOT 예
일반적으로 교차 집계 보고서를 생성하여 데이터를 요약하려는 경우 PIVOT을 사용하면 유용합니다. 예를 들어 AdventureWorks2008R2 예제 데이터베이스의 PurchaseOrderHeader 테이블을 쿼리하여 특정 직원의 구매 주문 수를 파악하려고 합니다. 다음 쿼리에서는 이 보고서를 공급업체별로 제공합니다.
USE AdventureWorks2008R2;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;
다음은 결과 집합의 일부입니다.
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1492 2 5 4 4 4
1494 2 5 4 5 4
1496 2 4 4 5 5
1498 2 5 4 4 4
1500 3 4 4 5 4
이 하위 SELECT 문에서 반환하는 결과는 EmployeeID 열에서 피벗됩니다.
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;
이는 EmployeeID 열에서 반환하는 각 고유 값이 최종 결과 집합의 필드가 됨을 의미합니다. 이에 따라 피벗 절에 지정된 각 EmployeeID 번호에 대해 열이 제공되며 여기서는 직원 164, 198, 223, 231 및 233에 대해 열이 하나씩 제공됩니다. PurchaseOrderID 열은 최종 출력에 반환되는 열(그룹화 열)을 그룹화하는 기준 값 열로 사용됩니다. 이 경우 그룹화 열은 COUNT 함수로 집계됩니다. PurchaseOrderID 열에 표시되는 Null 값이 각 직원에 대한 COUNT 계산 시 사용되지 않았다는 경고 메시지가 나타납니다.
.gif "중요 정보") 중요 |
|---|
집계 함수에 PIVOT을 사용하면 집계 계산 시 값 열의 모든 NULL 값이 사용되지 않습니다. |
UNPIVOT은 열을 행으로 회전하여 PIVOT과 거의 반대되는 연산을 수행합니다. 위의 예에서 생성된 테이블이 데이터베이스에 pvt로 저장되어 있는 상태에서 Emp1, Emp2, Emp3, Emp4 및 Emp5 열 식별자를 특정 공급업체에 해당하는 행 값으로 회전하려고 한다고 가정합니다. 이는 추가로 두 열을 식별해야 함을 의미합니다. 회전할 열 값(Emp1, Emp2 등)이 포함된 열을 Employee라고 하고 회전할 열 아래의 현재 값이 포함될 열을 Orders라고 합니다. 이 두 열은 각각 Transact-SQL 정의에서 pivot_column과 value_column에 해당합니다. 쿼리는 다음과 같습니다.
--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
...
UNPIVOT이 PIVOT의 정반대는 아닙니다. PIVOT은 집계를 수행하고 출력에서 가능한 여러 행을 단일 행으로 병합합니다. 행이 병합되었기 때문에 UNPIVOT은 원래 테이블 반환 식 결과를 다시 생성하지 않습니다. 또한 UNPIVOT 입력의 NULL 값은 출력에 나타나지 않지만 PIVOT 연산 전 입력에 원래 NULL 값이 있을 수 있습니다.
AdventureWorks2008R2 예제 데이터베이스의 Sales.vSalesPersonSalesByFiscalYears 뷰는 PIVOT을 사용하여 각 영업 사원의 총 매출액을 회계 연도별로 반환합니다. 뷰를 스크립팅하려면 SQL Server Management Studio의 개체 탐색기에 있는 AdventureWorks2008R2 데이터베이스에 대한 뷰 폴더에서 뷰를 찾습니다. 뷰 이름을 마우스 오른쪽 단추로 클릭한 다음 뷰 스크립팅을 선택합니다.