아래 답변 달아주셔서 잘 해결하였으나 요구사항이 바뀌면서 다시 질문드립니다..
SELECT '20151108' AS HSS_DD, '32' AS DUTY_ID, '1' AS DUTY, 'A' AS SHT FROM DUAL UNION ALL
SELECT '20151108', '32', '2', 'B' FROM DUAL UNION ALL
SELECT '20151108', '32', '3', 'C' FROM DUAL UNION ALL
SELECT '20151108', '33', '1', 'A' FROM DUAL UNION ALL
SELECT '20151108', '33', '2', 'B' FROM DUAL UNION ALL
SELECT '20151108', '33', '3', 'C' FROM DUAL UNION ALL
SELECT '20151108', '43', '1', 'A' FROM DUAL UNION ALL
SELECT '20151108', '43', '2', 'B' FROM DUAL UNION ALL
SELECT '20151108', '43', '3', 'C' FROM DUAL UNION ALL
SELECT '20151109', '32', '1', 'A' FROM DUAL UNION ALL
SELECT '20151109', '32', '2', 'B' FROM DUAL UNION ALL
SELECT '20151109', '32', '3', 'C' FROM DUAL UNION ALL
SELECT '20151109', '33', '1', 'A' FROM DUAL UNION ALL
SELECT '20151109', '33', '2', 'B' FROM DUAL UNION ALL
SELECT '20151109', '33', '3', 'C' FROM DUAL UNION ALL
SELECT '20151109', '43', '1', 'A' FROM DUAL UNION ALL
SELECT '20151109', '43', '2', 'B' FROM DUAL UNION ALL
SELECT '20151109', '43', '3', 'C' FROM DUAL UNION ALL
SELECT '20151109', '43', 'D', 'C' FROM DUAL UNION ALL
SELECT '20151110', '32', '1', 'A' FROM DUAL UNION ALL
SELECT '20151110', '32', '2', 'B' FROM DUAL UNION ALL
SELECT '20151110', '32', '3', 'C' FROM DUAL UNION ALL
SELECT '20151110', '33', '1', 'A' FROM DUAL UNION ALL
SELECT '20151110', '33', '2', 'B' FROM DUAL UNION ALL
SELECT '20151110', '33', '3', 'C' FROM DUAL UNION ALL
SELECT '20151110', '43', '1', 'A' FROM DUAL UNION ALL
SELECT '20151110', '43', '2', 'B' FROM DUAL UNION ALL
SELECT '20151110', '43', '3', 'C' FROM DUAL
HSS_DD | DUTY_ID | DUTY | SHT |
20151108 | 32 | 1 | A |
20151108 | 32 | 2 | B |
20151108 | 32 | 3 | C |
20151108 | 33 | 1 | A |
20151108 | 33 | 2 | B |
20151108 | 33 | 3 | C |
20151108 | 43 | 1 | A |
20151108 | 43 | 2 | B |
20151108 | 43 | 3 | C |
20151109 | 32 | 1 | A |
20151109 | 32 | 2 | B |
20151109 | 32 | 3 | C |
20151109 | 33 | 1 | A |
20151109 | 33 | 2 | B |
20151109 | 33 | 3 | C |
20151109 | 43 | 1 | A |
20151109 | 43 | 2 | B |
20151109 | 43 | 3 | C |
20151109 | 43 | D | C |
20151110 | 32 | 1 | A |
20151110 | 32 | 2 | B |
20151110 | 32 | 3 | C |
20151110 | 33 | 1 | A |
20151110 | 33 | 2 | B |
20151110 | 33 | 3 | C |
20151110 | 43 | 1 | A |
20151110 | 43 | 2 | B |
20151110 | 43 | 3 | C |
위 테이블처럼 동일날짜에 DUTY_ID 값이 3개씩 존재합니다...
해서 결과값을
SUN_32 | SUN_33 | SUN_43 | MON_32 | MON_33 | MON_43 | TUE_32 | TUE_33 | TUE_43 |
32 | 33 | 43 | 32 | 33 | 43 | 32 | 33 | 43 |
A | A | A | A | A | A | A | A | A |
B | B | B | B | B | B | B | B | B |
C | C | C | C | C | C | C | C | C |
D |
이렇게 뽑아야 하는데 가능할지요..
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SELECT * FROM ( SELECT TO_CHAR(TO_DATE(hss_dd, 'yyyymmdd' ), 'd' ) || duty_id d , duty , sht FROM t WHERE hss_dd >= TO_CHAR(TRUNC(sysdate, 'd' ) , 'yyyymmdd' ) AND hss_dd <= TO_CHAR(TRUNC(sysdate, 'd' ) + 6, 'yyyymmdd' ) ) PIVOT ( MIN (sht) FOR d IN ( '132' sun_32, '133' sun_33, '143' sun_43 , '232' mon_32, '233' mon_33, '243' mon_43 , '332' tue_32, '333' tue_33, '343' tue_43 , '432' wed_32, '433' wed_33, '443' wed_43 , '532' thu_32, '533' thu_33, '543' thu_43 , '632' fri_32, '633' fri_33, '643' fri_43 , '732' sat_32, '733' sat_33, '743' sat_43 ) ) ORDER BY duty ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT * FROM ( SELECT TO_CHAR(TO_DATE(hss_dd, 'yyyymmdd' ), 'd' ) d , duty_id , duty , sht FROM t WHERE hss_dd >= TO_CHAR(TRUNC(sysdate, 'd' ) , 'yyyymmdd' ) AND hss_dd <= TO_CHAR(TRUNC(sysdate, 'd' ) + 6, 'yyyymmdd' ) ) PIVOT ( MIN (sht) FOR duty_id IN (32, 33, 43)) PIVOT ( MIN ( "32" ) "32" , MIN ( "33" ) "33" , MIN ( "43" ) "43" FOR d IN (1 sun, 2 mon, 3 tue, 4 wed, 5 thu, 6 fri, 7 sat) ) ORDER BY duty ; |