[DB][MSSQL] PIVOT, UNPIVOT으로 여러 컬럼 합치기

최근 운영 업무를 하며

 

두 컬럼을 하나의 컬럼으로 합쳐

 

DB 데이터를 가공하여 조회할 일이 있었다.

 

 

저렇게 말만 써놓으니까 헷갈리는데,

 

나랑 비슷한 고민을 한 okky의 글을 참고하면 된다.

 

 

내가 하고자 했던 것과 똑같은 질문이고,

 

해당 글 가장 아래 답변에 쿼리가 작성되어 있다.

 

---

 

(AS-IS) 데이터 포맷

내가 가공하려던 기존 데이터 포맷은 아래와 같다.

 

TYPE

PLANT

TOTAL

M1

M2

M3

M4

M5

M6

M7

M8

M9

M 10

M 11

M 12

1

A

China

25

3

4

5

2

B

China

3

C

China

4

D

China

5

E

China

6

A

Brazil

7

B

Brazil

8

C

Brazil

9

D

Brazil

10

E

Brazil

 

어우, TOTAL부터 M1~M12까지의 숫자 데이터 치기 귀찮다.

 

 

하여튼 여러 PLANT가 존재하고,

 

각 사업장마다 여러 TYPE이 존재하며,

 

각 사업장마다 1월부터 12월의 데이터,

 

그리고 그 데이터들을 합친 TOTAL이 존재한다.

 

 

(TO-BE) 데이터 포맷

위의 데이터 포맷을 아래 포맷으로 바꾸고 싶다.

 

	PLANT	A_TOTAL	A_M1	...	B_TOTAL	B_M1	...	C_TOTAL	...
1	China
2	Brazil

 

깔끔해지지만 컬럼이 굉장히 많이 늘어난다.

 

 

예를 들어,

 

5개의 PLANT, 5개의 TYPE이 있다고 가정하면

 

 

한 개의 사업장마다

 

5 TYPE * (12 Month + 1 Total) = 65,

 

무려 65개의 컬럼이 생성된다.

 

 

하지만 내가 만질 데이터는

 

그 정도 컬럼 수는 커버할 수 있을 정도로

 

많은 편은 아니어서 괜찮았다.

 

 

Query 문 - PIVOT + UNPIVOT

아래 쿼리문은 okky 질문글 답변에 적힌 것이다.

 

 

내가 작성한 쿼리 문도 아래 쿼리문과 99% 같다.

 

단지 컬럼 이름들만 바꿨을 뿐.

 

 

먼저 okky 글의 질문 내용을 읽고 와야 이해가 쉽다.

 

WITH DATA (DOC_NO, OPERCODE, ITEMCODE, STIME, ETIME) AS
(
	SELECT '40138', 'A', '11W070BH0', '2018-01-22 16:10:58.000', '2018-01-22 16:11:03.213' UNION ALL
	SELECT '40138', 'A', '11W220TTM', '2018-01-22 16:10:41.590', '2018-01-22 16:10:56.407' UNION ALL
	SELECT '40138', 'C', '11W070BH0', '2018-01-22 16:08:59.960', '2018-01-22 16:09:27.877' UNION ALL
	SELECT '40138', 'C', '11W220TTM', '2018-01-22 16:08:59.960', '2018-01-22 16:09:27.877' UNION ALL
	SELECT '40138', 'P', '11W070BH0', '2018-01-22 16:09:36.813', '2018-01-22 16:09:50.783' UNION ALL
	SELECT '40138', 'P', '11W220TTM', '2018-01-22 16:09:36.813', '2018-01-22 16:09:50.783' UNION ALL
	SELECT '40138', 'T', '11W070BH0', '2018-01-22 16:11:30.500', '2018-01-22 16:11:35.417' UNION ALL
	SELECT '40138', 'T', '11W220TTM', '2018-01-22 16:11:19.223', '2018-01-22 16:11:29.203' UNION ALL
	SELECT '40138', 'W', '11W070BH0', '2018-01-22 16:10:04.393', '2018-01-22 16:10:16.440' UNION ALL
	SELECT '40138', 'W', '11W220TTM', '2018-01-22 16:10:04.393', '2018-01-22 16:10:16.440'
)

SELECT *
FROM (
	SELECT DOC_NO, ITEMCODE, [TIME],
	OPERCODE + 
	CASE [TYPE]
		WHEN 'STIME' THEN '_S'
		WHEN 'ETIME' THEN '_E'
	END AS OPER_CODE
	FROM DATA
	UNPIVOT (
		[TIME] FOR [TYPE] IN ([STIME],[ETIME])
	) AS P1
) AS A
PIVOT (
	MAX([TIME]) FOR OPER_CODE IN (
		[A_S],[A_E], [C_S],[C_E],
		[P_S],[P_E], [T_S],[T_E],
		[W_S],[W_E]	 
	)
) AS P2
--ORDER BY DOC_NO, ITEMCODE

 

WITH 구문은 질문자의 데이터를 임시 테이블로 만든 것이고,

 

주목해야 할 부분은 아래 SELECT 문 부터이다.

 

 

이 질문자는 내가 원했던 [A_TOTAL], [A_M1] 등의 컬럼 대신

 

[A_S], [A_E], [C_S], [C_E] 등으로 만들고 싶은 것이다.

 

 

먼저 FROM 절에 있는 서브 쿼리를 보면,

 

UNPIVOT을 사용하여

 

STIME, ETIME 열의 값을 행으로 바꾼다.

 

 

그리고 CASE 문을 사용하여 STIME, ETIME 마다

 

원하는 형태의 새로운 코드를 만들어주면,

 

새로운 코드의 각각에 알맞은 데이터가 매칭된다.

 

 

그 다음, 서브 쿼리가 끝나고나면

 

가장 아래에서 PIVOT을 실행하여

 

위에서 만들었던 새로운 형태의 코드들을

 

행에서 열로 바꾸어

 

결국 원하는 포맷을 만들어 낼 수 있다.

 

 

위에서 언급한 나의 경우에 대입해보면,

 

위 서브 쿼리 문의 [STIME], [ETIME] 대신

 

[TOTAL], [M1], ... , [M12] 를 넣고,

 

 

서브 쿼리 문의 CASE 문에서도

 

내가 원하는 형태의 열 이름으로 바꾸고([A_TOTAL], [A_M1]),

 

 

가장 아래 PIVOT 문의 FOR 문에서

 

원하는 형태의 열 이름을 다 적어주면 된다.

 

---

 

쿼리가 꽤나 길어지게 되겠지만,

 

내가 찾을 수 있는 최선의 방법이었다.

 

 

끝!