FROM - PIVOT 및 UNPIVOT 사용

•  
• PIVOT 및 UNPIVOT 관계 연산자를 사용하여 테이블 반환 식을 다른 테이블로 변경할 수 있습니다. 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)이 필요합니다.

기본 PIVOT 예

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

USE AdventureWorks2014 ;  
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 값이 열 제목이 되도록 피벗됩니다. 또한 결과가 [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 예제 데이터베이스의 AdventureWorks2014 테이블을 쿼리하여 특정 직원의 구매 주문 수를 파악하려고 합니다. 다음 쿼리에서는 이 보고서를 공급업체별로 제공합니다.

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

USE AdventureWorks2014;  
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 번호에 대해 열이 제공되며 여기서는 직원 250, 251, 256, 257 및 260에 대해 열이 하나씩 제공됩니다. PurchaseOrderID 열은 최종 출력에 반환되는 열(그룹화 열)을 그룹화하는 기준 값 열로 사용됩니다. 이 경우 그룹화 열은 COUNT 함수로 집계됩니다. PurchaseOrderID 열에 표시되는 Null 값이 각 직원에 대한 COUNT 계산 시 사용되지 않았다는 경고 메시지가 나타납니다.

UNPIVOT 예

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 값이 있었을 수 있음을 나타냅니다.

AdventureWorks2012 예제 데이터베이스의 Sales.vSalesPersonSalesByFiscalYears 뷰는 PIVOT을 사용하여 각 영업 사원의 총 매출액을 회계 연도별로 반환합니다. 뷰를 스크립팅하려면 SQL Server Management Studio의 개체 탐색기에 있는 AdventureWorks2012 데이터베이스에 대한 뷰 폴더에서 뷰를 찾습니다. 뷰 이름을 마우스 오른쪽 단추로 클릭한 다음, 뷰 스크립팅을 선택합니다.

[출처] https://docs.microsoft.com/ko-kr/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15