FROM - PIVOT 및 UNPIVOT 사용

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System(PDW)

관계형 연산자와 UNPIVOT 관계형 연산자를 사용하여 PIVOT 테이블 반환 식을 다른 테이블로 변경할 수 있습니다. PIVOT 는 식의 한 열에서 출력의 여러 열로 고유 값을 전환하여 테이블 반환 식을 회전합니다. 그리고 PIVOT 최종 출력에서 원하는 나머지 열 값에 필요한 집계를 실행합니다. UNPIVOT 는 테이블 반환 식의 열을 열 값으로 회전하여 PIVOT과 반대 작업을 수행합니다.

구문 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>;  

설명

UNPIVOT 절의 열 식별자는 카탈로그 데이터 정렬을 따릅니다. SQL Database의 경우 데이터 정렬은 항상 SQL_Latin1_General_CP1_CI_AS입니다. 부분적으로 포함된 SQL Server 데이터베이스의 경우 데이터 정렬은 항상 Latin1_General_100_CI_AS_KS_WS_SC입니다. 열이 다른 열과 결합되면 충돌을 피하기 위해 collate 절(COLLATE DATABASE_DEFAULT)이 필요합니다.

Microsoft Fabric 및 Azure Synapse Analytics 풀에서 PIVOT 연산자가 아닌 열 출력에 GROUP BY가 있는 경우 PIVOT 연산자가 있는 쿼리는 실패합니다. 해결 방법으로 GROUP BY에서 피벗이 아닌 열을 제거합니다. 이 GROUP BY 절이 중복되므로 쿼리 결과는 동일합니다.

기본 PIVOT 예제

다음 코드 예제에서는 4개의 행이 있는 2열 테이블을 생성합니다.

USE AdventureWorks2022;  
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

세 가지로 정의된 DaysToManufacture제품은 없습니다.

다음 코드는 값이 열 머리글이 되도록 DaysToManufacture 피벗된 동일한 결과를 표시합니다. 결과가 3일 동안 [3] 제공되더라도 열이 NULL제공됩니다.

-- 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 일반적인 시나리오는 데이터 요약을 제공하기 위해 테이블 간 보고서를 생성하려는 경우입니다. 예를 들어 샘플 데이터베이스의 PurchaseOrderHeader 테이블을 AdventureWorks2022 쿼리하여 특정 직원이 주문한 구매 주문 수를 확인하려는 경우를 가정해 보겠습니다. 다음 쿼리에서는 이 보고서를 공급업체별로 제공합니다.

USE AdventureWorks2022;  
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

이 하위 선택 문에서 반환된 결과는 열에 EmployeeID 피벗됩니다.

SELECT PurchaseOrderID, EmployeeID, VendorID  
FROM PurchaseOrderHeader;  

열에서 반환된 EmployeeID 고유 값은 최종 결과 집합의 필드가 됩니다. 따라서 피벗 절에 지정된 각 EmployeeID 숫자에 대한 열이 있습니다. 이 경우 직원250, 251, 256257및 260. PurchaseOrderID 열은 최종 출력에 반환되는 열(그룹화 열)을 그룹화하는 기준 값 열로 사용됩니다. 이 경우 그룹화 열은 COUNT 함수로 집계됩니다. 각 직원에 대해 계산 COUNT 할 때 열에 PurchaseOrderID 표시되는 null 값이 고려되지 않았음을 나타내는 경고 메시지가 나타납니다.

Important

집계 함수를 사용하는 PIVOT경우 집계를 계산할 때 값 열에 null 값이 있는 것은 고려되지 않습니다.

UNPIVOT 예제

UNPIVOT은 열을 행으로 회전하여 PIVOT과 거의 반대되는 작업을 수행합니다. 위의 예에서 생성된 테이블이 데이터베이스에 pvt로 저장되어 있는 상태에서 Emp1, Emp2, Emp3, Emp4 및 Emp5 열 식별자를 특정 공급업체에 해당하는 행 값으로 회전하려고 한다고 가정합니다. 따라서 두 개의 추가 열을 식별해야 합니다. 회전하는 열 값(Emp1Emp2,...)을 포함하는 열이 호출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 값이 있었을 수 있음을 보여줍니다.

샘플 데이터베이스의 뷰는 Sales.vSalesPersonSalesByFiscalYears 각 회계 연도에 대해 각 영업 사원의 총 매출을 반환하는 데 사용합니다PIVOT.AdventureWorks2022 SQL Server Management Studio의 뷰를 스크립깅하려면 개체 탐색기에서 데이터베이스의 Views 폴더 AdventureWorks2022 아래에서 보기를 찾습니다. 보기 이름을 마우스 오른쪽 단추로 클릭한 다음 스크립트 보기를 선택합니다.

다음 단계

• FROM(Transact-SQL)
• CASE(Transact-SQL)