MSSQL 로우를 컬럼으로

[문제] [DATA 1]을 [DATA 2]로 변경

 

[DATA 1] 날짜       구분 명 20080904 가입 3 20080904 구매 2 20080905 가입 548 20080906 가입 955 20080907 가입 1080 20080908 가입 852 20080909 가입 327 20080910 가입 111

[DATA 2] 날짜       가입  구매 20080904  3      2 20080905 548    0 20080906 955    0 20080907 1080   0 20080908 852    0 20080909 327    0 20080910 111    0

 

 

[풀이]

SELECT 
 *
FROM (
 SELECT
  YYYYMMDD 
  ,CASE PRODUCT_NAME 
   WHEN '무료회원가입' THEN 'REG'
   ELSE 'SALE'
   END AS NAME
  ,CASE PRODUCT_NAME 
   WHEN '무료회원가입' THEN count(1)
   ELSE count(1)
  END AS CNT
 FROM TLINKPRICE
 WHERE YYYYMMDD BETWEEN '20080904' AND '20080910'
 GROUP BY YYYYMMDD, PRODUCT_NAME) T
PIVOT (SUM(CNT) FOR NAME IN ([REG], [SALE])) AS PVT

 

[펌 > 출처 : http://altibase.egloos.com/2247257]

 

MS SQL Server 2005에서새롭게 선보인 PIVOT 연산자에 대해 살펴보도록 하겠습니다. PIVOT 연산자와 비교되는 것이 UNPIVOT 연산자 입니다.UNPIVOT 연산자는 다음 강좌에서 살펴 보도록 하겠습니다.

1. PIVOT 연산자?

PIVOT 연산자가 생소하게 들리는 분들도 있습니다. 이해를 돕기 위해 다음 그림을 살펴 보도록 하겠습니다.

 
[그림 1]

[그림 1]에서 왼쪽 테이블과 같은 결과값이 PIVOT 연산자를 통해 오른쪽 테이블과 같은 결과값으로 변형시키게 됩니다. 왼쪽테이블의 상품명의 결과 값인 '자전거'와 '카메라'가 오른쪽 결과 테이블에서는 '자전거'와 '카메라'라는 컬럼이 되었습니다.그리고 왼쪽 테이블의 수량 값이 오른쪽 테이블에는 고객별 '자전거'와 '카메라'에 대한 수량의 합으로 변해 있습니다. 이와 같이결과 값의 특정 부분을 컬럼으로 바꾸고 집계 합수를 통해 재계산된 값을 표시하는 것이 PIVOT 연산자입니다. 사용예를 보면서살펴보도록 하겠습니다.

2. PIVOT 연산자 사용 예

간단한 예일 수 있지만 PIVOT 연산자의 사용 예를 살펴 보도록 하겠습니다. 우선 다음과 같이 [회원], [상품], [주문] 테이블을 만들고 값을 입력했습니다.

USE SQLWorld GO -- 회원 테이블 만들기 CREATE TABLE 회원 ( 회원번호 char(02) PRIMARY KEY, 회원명 varchar(20) ) -- 상품 테이블 만들기 CREATE TABLE 상품 ( 상품번호 char(02) PRIMARY KEY, 상품명 varchar(20) ) -- 주문 테이블 만들기 CREATE TABLE 주문 ( 주문번호 int PRIMARY KEY, 회원번호 char(02), 상품번호 char(02), 수량 int ) -- 데이터 입력 -- 회원 정보 입력 INSERT INTO 회원 VALUES ('M1', '홍길동') INSERT INTO 회원 VALUES ('M2', '김주몽') INSERT INTO 회원 VALUES ('M3', '이장래') INSERT INTO 회원 VALUES ('M4', '한국인') INSERT INTO 회원 VALUES ('M5', '오감자') -- 상품정보 입력 INSERT INTO 상품 VALUES ('P1', '자전거') INSERT INTO 상품 VALUES ('P2', '카메라') INSERT INTO 상품 VALUES ('P3', '노트북') -- 주문 정보 입력 INSERT INTO 주문 VALUES (1, 'M1', 'P1', 1) INSERT INTO 주문 VALUES (2, 'M2', 'P2', 2) INSERT INTO 주문 VALUES (3, 'M3', 'P1', 1) INSERT INTO 주문 VALUES (4, 'M3', 'P1', 1) INSERT INTO 주문 VALUES (5, 'M2', 'P3', 1) INSERT INTO 주문 VALUES (6, 'M1', 'P2', 3) INSERT INTO 주문 VALUES (7, 'M3', 'P1', 1) INSERT INTO 주문 VALUES (8, 'M1', 'P1', 2) INSERT INTO 주문 VALUES (9, 'M2', 'P3', 1) INSERT INTO 주문 VALUES (10, 'M1', 'P2', 1) GO

이 상태에서 회원별 상품 주문 현황을 보려면 다음과 같이 수행하면 됩니다.

-- 회원별+상품별 구매 정보 SELECT T2.회원명, T3.상품명, SUM(수량) 수량합 FROM 주문 T1  INNER JOIN 회원 T2 ON T1.회원번호 = T2.회원번호 INNER JOIN 상품 T3 ON T1.상품번호 = T3.상품번호 GROUP BY T2.회원명, T3.상품명

위 쿼리문의 수행 결과는 다음과 같습니다.

회원명 상품명 수량합 -------------------- -------------------- ----------- 김주몽 노트북 2 이장래 자전거 3 홍길동 자전거 3 김주몽 카메라 2 홍길동 카메라 4 (5개 행 적용됨)

위 결과를 상품명의 결과 값인 '노트북', '자전거', '카메라'를 컬럼으로 갖는 형태의 결과를 얻고자 한다면 다음과 같이 PIVOT 연산자를 활용하면 됩니다.

-- PIVOT 연산자 사용 SELECT * FROM (SELECT T2.회원명, T3.상품명, SUM(수량) 수량합 FROM 주문 T1  INNER JOIN 회원 T2 ON T1.회원번호 = T2.회원번호 INNER JOIN 상품 T3 ON T1.상품번호 = T3.상품번호 GROUP BY T2.회원명, T3.상품명) T PIVOT (SUM(수량합) FOR 상품명 IN ([자전거], [카메라], [노트북])) AS PVT

굵은 글씨체로 되어 있는 부분이 기존의 쿼리문에 추가된 내용입니다. 특히 마지막 줄의 PIVOT 연산자 부분을 관심있게 보시기 바랍니다.FOR 상품명 IN ([자전거], [카메라], [노트북]) 이 부분이 결과 값을 컬럼으로 변형 시키는 부분이고 SUM(수량합) 부분이 SUM() 함수를 이용해 새롭게 값을 계산하는 부분입니다. 물론 위 예제에서는 SUM() 함수가 큰 역할을 하지 못하네요.

결과는 다음과 같습니다.

회원명 자전거 카메라 노트북 -------------------- ----------- ----------- ----------- 김주몽 NULL 2 2 이장래 3 NULL NULL 홍길동 3 4 NULL (3개 행 적용됨)

3. 정리

만일 PIVOT 연산자가 없다는 마지막에 얻은 결과를 얻기 위해서 복잡한 쿼리문을 작성해야 합니다. MS SQL Server2005에 새롭게 선보인 향상된 Transact-SQL 구문은 이처럼 쿼리문을 간단하게 할 수 있는 편리함을 제공해주고있습니다. 다음 강좌에서는 PIVOT 연산자와 반대의 기능을 담당하는 UNPIVOT 연산자를 상펴보도록 하겠습니다.