최근 운영 업무를 하며
두 컬럼을 하나의 컬럼으로 합쳐
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 문에서
원하는 형태의 열 이름을 다 적어주면 된다.
쿼리가 꽤나 길어지게 되겠지만,
내가 찾을 수 있는 최선의 방법이었다.
끝!
'Study > Database' 카테고리의 다른 글
[DB] DCL, DDL, DML이란? (0) | 2021.06.02 |
---|---|
[DB] 인덱스란? - (1) 개념, 장단점, 쓰는 이유 (0) | 2021.05.31 |
[DB] SQL 작성 표준 가이드 (0) | 2021.02.25 |
[DB] 트랜잭션(Transaction)이란? (+ ACID) (0) | 2021.02.12 |
[DB][MSSQL] 저장 프로시저(Stored Procedure)란? (0) | 2021.01.22 |