SELECT '이름1' AS USER_NAME , '라인1' AS LINE, '1' AS SHIFT FROM DUAL UNION ALL SELECT '이름2' AS USER_NAME , '라인1' AS LINE, '1' AS SHIFT FROM DUAL UNION ALL SELECT '이름3' AS USER_NAME , '라인1' AS LINE, '1' AS SHIFT FROM DUAL UNION ALL SELECT '이름4' AS USER_NAME , '라인1' AS LINE, '2' AS SHIFT FROM DUAL UNION ALL SELECT '이름5' AS USER_NAME , '라인1' AS LINE, '2' AS SHIFT FROM DUAL UNION ALL SELECT '이름6' AS USER_NAME , '라인1' AS LINE, '2' AS SHIFT FROM DUAL UNION ALL SELECT '이름7' AS USER_NAME , '라인1' AS LINE, '3' AS SHIFT FROM DUAL UNION ALL SELECT '이름8' AS USER_NAME , '라인2' AS LINE, '1' AS SHIFT FROM DUAL UNION ALL SELECT '이름9' AS USER_NAME , '라인2' AS LINE, '1' AS SHIFT FROM DUAL UNION ALL SELECT '이름10' AS USER_NAME , '라인2' AS LINE, '2' AS SHIFT FROM DUAL UNION ALL SELECT '이름11' AS USER_NAME , '라인2' AS LINE, '3' AS SHIFT FROM DUAL
USER_NAME | LINE | SHIFT |
이름1 | 라인1 | 1 |
이름2 | 라인1 | 1 |
이름3 | 라인1 | 1 |
이름4 | 라인1 | 2 |
이름5 | 라인1 | 2 |
이름6 | 라인1 | 2 |
이름7 | 라인1 | 3 |
이름8 | 라인2 | 1 |
이름9 | 라인2 | 1 |
이름10 | 라인2 | 2 |
이름11 | 라인2 | 3 |
위와 같이 조회 되는 SQL 을 아래 형태로 결과를 조회 할 수 있도록 쿼리를 수정하고 싶은데 어떻게 하면 될지 문의드립니다.
LINE | SHIFT_1 | SHIFT_2 | SHIFT_3 |
라인1 | 이름1 | 이름4 | 이름7 |
라인1 | 이름2 | 이름5 | |
라인1 | 이름3 | 이름6 | |
라인2 | 이름8 | 이름10 | 이름11 |
라인2 | 이름9 |
WITH t AS ( SELECT '이름1' user_name, '라인1' line, 1 shift FROM dual UNION ALL SELECT '이름2' , '라인1', 1 FROM dual UNION ALL SELECT '이름3' , '라인1', 1 FROM dual UNION ALL SELECT '이름4' , '라인1', 2 FROM dual UNION ALL SELECT '이름5' , '라인1', 2 FROM dual UNION ALL SELECT '이름6' , '라인1', 2 FROM dual UNION ALL SELECT '이름7' , '라인1', 3 FROM dual UNION ALL SELECT '이름8' , '라인2', 1 FROM dual UNION ALL SELECT '이름9' , '라인2', 1 FROM dual UNION ALL SELECT '이름10', '라인2', 2 FROM dual UNION ALL SELECT '이름11', '라인2', 3 FROM dual ) SELECT * FROM (SELECT user_name, line, shift , ROW_NUMBER() OVER(PARTITION BY line, shift ORDER BY user_name) rn FROM t ) PIVOT (MIN(user_name) FOR shift IN (1 shift_1, 2 shift_2, 3 shift_3)) ORDER BY line, rn ;