데이터베이스 질문과 답변

다시 문의 드립니다.ㅜ 0 4 912

by zzhozang [2015.11.12 16:22:52]

아래 답변 달아주셔서 잘 해결하였으나 요구사항이 바뀌면서 다시 질문드립니다..

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 　 　 　

이렇게 뽑아야 하는데 가능할지요..

by 마농 [2015.11.12 17:13:20]

이미 32, 33, 43 으로 코드값이 고정된 상태라면?

첫번째줄 데이터는 없어도 될 듯 합니다.

이미 타이틀에 나오네요.

by zzhozang [2015.11.12 17:31:32]

네 해당 값은 꼭 필요한 부분은 아닙니다..

by 마농 [2015.11.12 17:38:49]
?
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
;

 

by zzhozang [2015.11.16 17:46:12]

정말 감사드립니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입