MSSQL PIVOT

PIVOT이라 함은 행을 열로 바꾸는 것을 의미한다.

만약 아래와 같은 테이블이 있다고 가정해보자.

과일이름	과일 개수
사과	10
바나나	20
샤인머스캣	30
복숭아	10
레몬	20
귤	30
오렌지	10

예제 데이터는 아래와같이 생성했다.

 

 

DECLARE @TEMP TABLE (
	FRUIT_NAME NVARCHAR(20),
	BUY_AMT INT
)

INSERT INTO @TEMP VALUES('사과',10)
INSERT INTO @TEMP VALUES('바나나',20)
INSERT INTO @TEMP VALUES('샤인머스캣',30) 
INSERT INTO @TEMP VALUES('복숭아',10)
INSERT INTO @TEMP VALUES('레몬',20)
INSERT INTO @TEMP VALUES('귤',30)
INSERT INTO @TEMP VALUES('오렌지',10)

 

 

이 테이블을 PIVOT을 사용하여 아래와 같은 형태로 만드려고 한다.

과일이름	10	20	30
귤	NULL	NULL	30
레몬	NULL	20	NULL
바나나	NULL	20	NULL
복숭아	10	NULL	NULL
사과	10	NULL	NULL
샤인머스캣	NULL	NULL	30
오렌지	10	NULL	NULL

구문은 다음과 같다.

SELECT [컬럼] FROM [적용할 테이블] [TABLE_ALIAS]

PIVOT( 집계함수([컬럼]) FOR [컬럼] IN (PIVOT할 컬럼) ) [PIVOT_ALIAS]

​

실행 쿼리는

 

 

SELECT * FROM @TEMP T
PIVOT( SUM(BUY_AMT) FOR BUY_AMT IN ([10],[20],[30]) ) P

 

여기서 만약 테스트 데이터가

과일이름	과일 개수
사과	10
바나나	20
샤인머스캣	30
복숭아	10
레몬	20
귤	30
오렌지	10
오렌지	10

라고 가정했을 때 위의 방법으로 PIVOT을 돌리면 아래와 같이 나온다.

과일이름	10	20	30
귤	NULL	NULL	30
레몬	NULL	20	NULL
바나나	NULL	20	NULL
복숭아	10	NULL	NULL
사과	10	NULL	NULL
샤인머스캣	NULL	NULL	30
오렌지	20	NULL	NULL

오렌지가 10 컬럼에서 20개인 것을 볼 수 있다.

따라서 PIVOT을 사용할 때 집계할 컬럼이 있다면

아래와 같이 사용해주는 것이 좋다.

​

실행 쿼리는

 

 

SELECT * FROM (SELECT FRUIT_NAME,SUM(BUY_AMT) BUY_AMT FROM @TEMP GROUP BY FRUIT_NAME) T
PIVOT( SUM(BUY_AMT) FOR BUY_AMT IN ([10],[20],[30]) ) P

 

 

말로 풀어 설명하자면

집계할 컬럼에 대해서는 PIVOT을 적용할 테이블에서 미리 집계를 한 후 PIVOT을 하는 것이다.

​

여기서 추가로 응용을 해보자면

10	20	30
복숭아	레몬	귤
사과	바나나	샤인머스캣
NULL	오렌지	NULL

데이터가 위처럼 각 각의 양에 맞는 컬럼에 과일 이름이 들어가야 하며 NULL을 최소화시켜야한다고 가정을 했을 때

MSSQL ROW_NUMBER 포스팅에서 했던 것처럼 ROW_NUMBER함수를 사용하여 SQL문을 작성할 수 있다.

​

실행 쿼리는 아래와 같다.

 

 

 

 

SELECT 
		MAX(CASE WHEN BUY_AMT = 10 THEN FRUIT_NAME END ) [10]
	,	MAX(CASE WHEN BUY_AMT = 20 THEN FRUIT_NAME END ) [20]
	,	MAX(CASE WHEN BUY_AMT = 30 THEN FRUIT_NAME END ) [30]
FROM (
	SELECT 
			FRUIT_NAME
		,	BUY_AMT
		,	ROW_NUMBER() OVER(PARTITION BY BUY_AMT ORDER BY BUY_AMT) ROWNUM
	FROM (
		SELECT 
				FRUIT_NAME
			,	SUM(BUY_AMT) BUY_AMT
		FROM @TEMP
		GROUP BY FRUIT_NAME
	) A
) A
GROUP BY ROWNUM