PIVOT 및 UNPIVOT 사용
PIVOT 및 UNPIVOT 관계 연산자를 사용하여 테이블 값 식을 다른 테이블로 조작할 수 있습니다. PIVOT은 식의 한 열에 포함된 여러 고유 값을 출력에서 여러 열로 바꿔 테이블 값 식을 회전하고 최종 출력에서 남은 열 값 중 원하는 값에 대해 필요에 따라 집계를 수행합니다. UNPIVOT은 테이블 값 식의 열을 열 값으로 회전하여 PIVOT과 반대되는 연산을 수행합니다.
 |
|---|
| Microsoft SQL Server 2005 로 업그레이드되는 데이터베이스에 대해 PIVOT 및 UNPIVOT을 사용할 때 데이터베이스의 호환성 수준을 90으로 설정해야 합니다. 데이터베이스 호환성 수준을 설정하려면sp_dbcmptlevel(Transact-SQL)을 참조하십시오. |
PIVOT은 복잡한 일련의 SELECT...CASE 문에서 다른 방법으로 지정하는 구문보다 단순하고 읽기 쉬운 구문을 제공합니다.
PIVOT이 유용한 일반적인 시나리오는 교차 집계 보고서를 생성하여 데이터를 요약하려는 경우입니다. 예를 들어AdventureWorks예제 데이터베이스의PurchaseOrderHeader테이블을 쿼리하여 특정 직원의 구매 주문 수를 파악하려고 합니다. 다음 쿼리에서는 이 보고서를 공급업체별로 나눠 제공합니다.
USE AdventureWorks;GOSELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5FROM (SELECT PurchaseOrderID, EmployeeID, VendorIDFROM Purchasing.PurchaseOrderHeader) pPIVOT(COUNT (PurchaseOrderID)FOR EmployeeID IN( [164], [198], [223], [231], [233] )) AS pvtORDER BY VendorID
다음은 결과 집합의 일부입니다.
VendorID Emp1 Emp2 Emp3 Emp4 Emp51 4 3 5 4 42 4 1 5 5 53 4 3 5 4 44 4 2 5 5 45 5 1 5 5 5
이 하위 SELECT 문에서 반환하는 결과는EmployeeID열에서 피벗됩니다.
SELECT PurchaseOrderID, EmployeeID, VendorIDFROM PurchaseOrderHeader
이는EmployeeID열에서 반환하는 각 고유 값이 최종 결과 집합의 필드가 됨을 의미합니다. 따라서 피벗 절에 지정된EmployeeID번호마다 열이 하나씩 있습니다. 이 경우에는 직원 164, 198, 223, 231 및 233에 대한 열이 있습니다.PurchaseOrderID열은 값 열 역할을 하며 이에 대해 최종 출력에 반환되는 열이 그룹화됩니다. 이러한 열을 그룹화 열이라고 합니다. 이 경우 그룹화 열이 COUNT 함수로 집계됩니다.PurchaseOrderID열에 표시되는 NULL 값은 각 직원에 대한 COUNT 계산 시 사용되지 않았다는 경고 메시지가 나타납니다.
|
|---|
| 집계 함수에 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 above example.CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,Emp3 int, Emp4 int, Emp5 int)GOINSERT 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, OrdersFROM (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) pUNPIVOT (Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5))AS unpvtGO
다음은 결과 집합의 일부입니다.
VendorID Employee Orders1 Emp1 41 Emp2 31 Emp3 51 Emp4 41 Emp5 42 Emp1 42 Emp2 12 Emp3 52 Emp4 52 Emp5 5...
UNPIVOT이 PIVOT의 정반대는 아닙니다. PIVOT은 집계를 수행하고 출력에서 가능한 여러 행을 단일 행으로 병합합니다. 행이 병합되었기 때문에 UNPIVOT은 원래 테이블 값 식 결과를 다시 생성하지 않습니다. 또한 UNPIVOT 입력의 NULL 값은 출력에 나타나지 않지만 PIVOT 연산 전 입력에 원래 NULL 값이 있었을 수 있습니다.
AdventureWorks예제 데이터베이스의Sales.vSalesPersonSalesByFiscalYears뷰는 PIVOT을 사용하여 각 영업 사원의 총 매출액을 회계 연도별로 반환합니다. 뷰를 스크립팅하려면 SQL Server Management Studio 개체 탐색기에서AdventureWorks데이터베이스의뷰폴더에서 원하는 뷰를 찾습니다. 뷰 이름을 마우스 오른쪽 단추로 클릭하고뷰 스크립팅을 선택합니다.
Navigation