MSSQL – Pivot

MS SQL 서버 2005에서는 PIVOT 함수와 UNPIVOT 함수를 제공하고 있습니다. 이 기능을 이용하면 피벗 테이블을 쉽게 작성 할 수 있습니다. 하지만 기본적으로 제공되는 기능만을 이용하면 동적인 피벗 테이블을 구현 할 수 없습니다. 이를 해결 할 수 있는 방법 한 가지를 같이 살펴보고자 합니다.

1. 테이블 생성

다음과 같은 테스트에 사용할 테이블을 만들도록 하겠습니다.

    USE tempdb   
    GO   
    CREATE TABLE Orders (   
       Customer varchar(8),   
       Product varchar(5),   
       Quantity int  
    )   
    GO   
    INSERT INTO Orders VALUES(‘Mike’, ‘Bike’, 3)   
    INSERT INTO Orders VALUES(‘Mike’, ‘Chain’, 2)   
    INSERT INTO Orders VALUES(‘Mike’, ‘Bike’, 5)   
    INSERT INTO Orders VALUES(‘Lisa’, ‘Bike’, 3)   
    INSERT INTO Orders VALUES(‘Lisa’, ‘Chain’, 3)   
    INSERT INTO Orders VALUES(‘LIsa’, ‘Bike’, 4)   
    GO   
    SELECT * FROM Orders   
    GO  

마지막 SELECT 문의 결과는 다음과 같습니다.

    Customer Product Quantity   
    ——– ——- ———–   
    Mike     Bike    3   
    Mike     Chain   2   
    Mike     Bike    5   
    Lisa     Bike    3   
    Lisa     Chain   3   
    LIsa     Bike    4   
      
    (6개 행 적용됨)  

2. PIVOT 함수 이용

다음과 같이 PIVOT 함수릉 이용해 우선은 간단하나마 피벗 테이블을 만들어 낼 수 있습니다.

    SELECT * FROM Orders   
       PIVOT (SUM(Quantity) FOR Product IN (Bike, Chain)   
    ) AS PVT   
    GO  

결과는 다음과 같습니다.

    Customer Bike        Chain   
    ——– ———– ———–   
    Lisa     7           3   
    Mike     8           2   
      
    (2개 행 적용됨)  

(혹 PIVOT 함수에 대해 모르시는 분들은 [SQL배워보기]-[2005강좌]의 내용을 참고하시기 바랍니다)

여기서 고민하고자 하는 부분은 위 SELECT 문에서의 FOR Product IN (Bike, Chain) 부분입니다. 이 부분이 피벗 테이블 결과에서 가로로 나열될 컬럼을 지정해주게 되는게 Bike, Chain 처럼 고정된 값을 지정해 주어야 한다는 것입니다. 하지만 실제 업무에 있어서는 가로로 나열될 컬럼이 고정적이지 않을 수 있기 때문에 사용에 한계를 느낄 수 있게 됩니다. 예를 들어 다음과 같이 Orders 테이블에 행을 추가해 보도록 하겠습니다.

    INSERT INTO Orders VALUES(‘Mike’, ‘Chain’, 1)   
    INSERT INTO Orders VALUES(‘Mike’, ‘Light’, 2)   
    INSERT INTO Orders VALUES(‘Lisa’, ‘Chain’, 2)   
    INSERT INTO Orders VALUES(‘LIsa’, ‘Light’, 3)   
    GO  

위 INSERT 문으로 인해 기존의 Product 컬럼에는 없었던 Light 가 기록되게 됩니다. 하지만 다음과 같이 기존의 PIVOT문을 이용해 결과를 확인해 보면

    SELECT * FROM Orders   
       PIVOT (SUM(Quantity) FOR Product IN (Bike, Chain)   
    ) AS PVT   
    GO  

결과는 다음과 같습니다.

    Customer Bike        Chain   
    ——– ———– ———–   
    Lisa     7           5   
    Mike     8           3   
      
    (2개 행 적용됨)  

이 결과를 보면 추가로 입력된 Light 는 결과에 표시되지 않고 있습니다. 그럼 이 문제를 어떻게 해결 할까요?

3. Dynamic PIVOT 구현

여러가지 해결 방법이 있을 것이지만, 다음의 방법이 이 중 하나가 될 수 있습니다.

    DECLARE @Prod varchar(2000)   
    SET @Prod = ”  
    SELECT @Prod = @Prod + ‘[‘ + Product + ‘],’  
       FROM (SELECT Distinct Product FROM Orders) A   
      
    SET @Prod = LEFT(@Prod, LEN(@Prod) – 1)   
    EXEC (‘SELECT * FROM Orders  
            PIVOT ( SUM(Quantity) FOR Product IN (‘ + @Prod + ‘)  
            ) AS PVT’)   
    GO  

우선 Orders 테이블에서 모든 Product를 DISTINCT를 이용해 한번씩만 가져와 콤마(,)로 연결을 한 후 가장 마지막의 콤마만 제거한 문자열을 갖는 @Prod 변수를 IN 부분에 연결하여 수행하고자 하는 SELECT 문을 만들어 EXEC()를 이용해 수행한 것입니다. [ ]로 Product의 값을 묶어 준 이유는 Product 값에 공백 등이 들어 있을 경우에 대비한 것입니다. 결과는 다음과 같습니다.

    Customer Bike        Chain       Light   
    ——– ———– ———– ———–   
    Lisa     7           5           3   
    Mike     8           3           2   
      
    (2개 행 적용됨)  

위 결과 처럼 새로운 Product가 입력된다 하더라고 결과에 그 내용이 포함되어 표시됩니다.

4. 정리

간단하나마 MS SQL 서버 2005의 PIVOT 함수를 좀더 효율 적으로 이용하는 방법을 살펴 보았습니다. 분명 이보다 더 좋은 방법이 있을 수 있습니다. 유일한 방법이 아님을 기억해 주시기 바랍니다.

출처 : http://www.sqlworld.pe.kr/mboard/mboard/mboard.asp?board_id=tip&group_name=board&idx_num=20&page=1&category=&search=&b_cat=0&order_c=idx_num&order_da=asc