데이터베이스 질문과 답변
오라클 쿼리 질문드립니다.
0
1
4,737
by zzhozang [2024.07.24 09:58:59]
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 |
by 마농
[2024.07.24 12:00:08]
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
;
) 버튼을 클릭하여 작성 하시면 됩니다.