출처 :
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 함수를 좀더 효율 적으로 이용하는 방법을 살펴 보았습니다. 분명 이보다 더 좋은 방법이 있을 수 있습니다. 유일한 방법이 아님을 기억해 주시기 바랍니다.
'프로그램 > 프로그램관련' 카테고리의 다른 글
[스크랩] div 영역 프린트 출력하기 (0) | 2012.12.31 |
---|---|
[스크랩] [MS-SQL] 문법정리 (0) | 2012.12.27 |
Windows 7 SP1 ... (0) | 2011.03.01 |
Windows 7 - XP 호환모드 관련 (0) | 2010.06.20 |
스트리밍서버 (0) | 2010.02.17 |