데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

WITH A AS
(
SELECT SUBCENTERNM T1, MEDDEPTNM T2, SUBCENTERNM||'-'||MEDDEPTNM TT, decode(dday||ampm,'2A',MEDDRNM) W1, NULL W2, NULL W3, NULL W4, NULL W5, NULL W6, NULL W7, NULL W8, NULL W9, NULL W10, NULL W11, NULL W12
FROM APDRSCHT   --월/오전
UNION ALL
SELECT SUBCENTERNM T1, MEDDEPTNM T2, SUBCENTERNM||'-'||MEDDEPTNM TT, NULL W1, decode(dday||ampm,'2P',MEDDRNM) W2, NULL W3, NULL W4, NULL W5, NULL W6, NULL W7, NULL W8, NULL W9, NULL W10, NULL W11, NULL W12
FROM APDRSCHT   --월/오후 UNION ALL
SELECT SUBCENTERNM T1, MEDDEPTNM T2, SUBCENTERNM||'-'||MEDDEPTNM TT, NULL W1, NULL W2, decode(dday||ampm,'3A',MEDDRNM) W3, NULL W4, NULL W5, NULL W6, NULL W7, NULL W8, NULL W9, NULL W10, NULL W11, NULL W12
FROM APDRSCHT   --화/오전
UNION ALL
SELECT SUBCENTERNM T1, MEDDEPTNM T2, SUBCENTERNM||'-'||MEDDEPTNM TT, NULL W1, NULL W2, NULL W3, decode(dday||ampm,'3P',MEDDRNM) W4, NULL W5, NULL W6, NULL W7, NULL W8, NULL W9, NULL W10, NULL W11, NULL W12
FROM APDRSCHT   --화/오후 등등...
UNION ALL
SELECT SUBCENTERNM T1, MEDDEPTNM T2, SUBCENTERNM||'-'||MEDDEPTNM TT, NULL W1, NULL W2, NULL W3, NULL W4, decode(dday||ampm,'4A',MEDDRNM) W5, NULL W6, NULL W7, NULL W8, NULL W9, NULL W10, NULL W11, NULL W12
FROM APDRSCHT
UNION ALL
SELECT SUBCENTERNM T1, MEDDEPTNM T2, SUBCENTERNM||'-'||MEDDEPTNM TT, NULL W1, NULL W2, NULL W3, NULL W4, NULL W5, decode(dday||ampm,'4P',MEDDRNM) W6, NULL W7, NULL W8, NULL W9, NULL W10, NULL W11, NULL W12
FROM APDRSCHT
UNION ALL
SELECT SUBCENTERNM T1, MEDDEPTNM T2, SUBCENTERNM||'-'||MEDDEPTNM TT, NULL W1, NULL W2, NULL W3, NULL W4, NULL W5, NULL W6, decode(dday||ampm,'5A',MEDDRNM) W7, NULL W8, NULL W9, NULL W10, NULL W11, NULL W12
FROM APDRSCHT
UNION ALL
SELECT SUBCENTERNM T1, MEDDEPTNM T2, SUBCENTERNM||'-'||MEDDEPTNM TT, NULL W1, NULL W2, NULL W3, NULL W4, NULL W5, NULL W6, NULL W7, decode(dday||ampm,'5P',MEDDRNM) W8, NULL W9, NULL W10, NULL W11, NULL W12
FROM APDRSCHT
UNION ALL
SELECT SUBCENTERNM T1, MEDDEPTNM T2, SUBCENTERNM||'-'||MEDDEPTNM TT, NULL W1, NULL W2, NULL W3, NULL W4, NULL W5, NULL W6, NULL W7, NULL W8, decode(dday||ampm,'6A',MEDDRNM) W9, NULL W10, NULL W11, NULL W12
FROM APDRSCHT
UNION ALL
SELECT SUBCENTERNM T1, MEDDEPTNM T2, SUBCENTERNM||'-'||MEDDEPTNM TT, NULL W1, NULL W2, NULL W3, NULL W4, NULL W5, NULL W6, NULL W7, NULL W8, NULL W9, decode(dday||ampm,'6P',MEDDRNM) W10, NULL W11, NULL W12
FROM APDRSCHT
UNION ALL
SELECT SUBCENTERNM T1, MEDDEPTNM T2, SUBCENTERNM||'-'||MEDDEPTNM TT, NULL W1, NULL W2, NULL W3, NULL W4, NULL W5, NULL W6, NULL W7, NULL W8, NULL W9, NULL W10, decode(dday||ampm,'7A',MEDDRNM) W11, NULL W12
FROM APDRSCHT
UNION ALL
SELECT SUBCENTERNM T1, MEDDEPTNM T2, SUBCENTERNM||'-'||MEDDEPTNM TT, NULL W1, NULL W2, NULL W3, NULL W4, NULL W5, NULL W6, NULL W7, NULL W8, NULL W9, NULL W10, NULL W11, decode(dday||ampm,'7P',MEDDRNM) W12
FROM APDRSCHT
)
SELECT T1, T2, TT
, MAX( CASE WHEN CHK = 1 THEN W1 END ) W1
, MAX( CASE WHEN CHK = 2 THEN W1 END ) W2
, MAX( CASE WHEN CHK = 3 THEN W1 END ) W3
, MAX( CASE WHEN CHK = 4 THEN W1 END ) W4
, MAX( CASE WHEN CHK = 5 THEN W1 END ) W5
, MAX( CASE WHEN CHK = 6 THEN W1 END ) W6
, MAX( CASE WHEN CHK = 7 THEN W1 END ) W7
, MAX( CASE WHEN CHK = 8 THEN W1 END ) W8
, MAX( CASE WHEN CHK = 9 THEN W1 END ) W9
, MAX( CASE WHEN CHK = 10 THEN W1 END ) W10
, MAX( CASE WHEN CHK = 11 THEN W1 END ) W11
, MAX( CASE WHEN CHK = 12 THEN W1 END ) W12
FROM (
   SELECT T1, T2, TT, CHK, W1
    , ROW_NUMBER() OVER( PARTITION BY TT, CHK ORDER BY ROWNUM ) RN
     FROM (
      SELECT T1, T2, TT, W1, '1' CHK
        FROM A
       WHERE W1 IS NOT NULL
      UNION ALL
      SELECT T1, T2, TT, W2, '2' CHK
        FROM A
       WHERE W2 IS NOT NULL
      UNION ALL
      SELECT T1, T2, TT, W3, '3' CHK
        FROM A
       WHERE W3 IS NOT NULL
      UNION ALL
      SELECT T1, T2, TT, W4, '4' CHK
        FROM A
       WHERE W4 IS NOT NULL
      UNION ALL
      SELECT T1, T2, TT, W5, '5' CHK
        FROM A
       WHERE W5 IS NOT NULL
      UNION ALL
      SELECT T1, T2, TT, W6, '6' CHK
        FROM A
       WHERE W6 IS NOT NULL
      UNION ALL
      SELECT T1, T2, TT, W7, '7' CHK
        FROM A
       WHERE W7 IS NOT NULL
      UNION ALL
      SELECT T1, T2, TT, W8, '8' CHK
        FROM A
       WHERE W8 IS NOT NULL
      UNION ALL
      SELECT T1, T2, TT, W9, '9' CHK
        FROM A
       WHERE W9 IS NOT NULL
      UNION ALL
      SELECT T1, T2, TT, W10, '10' CHK
        FROM A
       WHERE W10 IS NOT NULL
      UNION ALL
      SELECT T1, T2, TT, W10, '11' CHK
        FROM A
       WHERE W11 IS NOT NULL
      UNION ALL
      SELECT T1, T2, TT, W10, '12' CHK
        FROM A
       WHERE W12 IS NOT NULL
     )
)
GROUP BY T1, T2, TT, RN
ORDER BY 1 DESC

센타명   지점명   월/오전    월/오후     화/오전    화/오후   수/오전     수/오후  .........   금까지
센타1    A지점    가가가     다다다                                                        차차차
센타1    A지점    나나나
센타1    B지점    라라라                                                      마마마
센타2    A지점                     바바바     사사사                     아아아
센타2    B지점                                                         자자자

결과값은 이처럼 원하는 대로 잘 나오고 있습니다.

현재까지 이 쿼리로 잘 사용하고 있었는데 아래 질문 글에 말씀드린 것 처럼 3TIRE 환경에서는 WITH 문이 컴파일이 안된다고 합니다.
쿼리를 바궈야 하는데 고맙게도 햇살조각님이 쿼리를 주셔서 적용하려 하니 쿼리량이 엄청 많아지는 군요. ㅜㅜ
이것 때문에 며칠 고생했는데 염치없지만 솔직히 도와달라고 말하고 싶습니다. 긴글 읽어 주셔서 감사합니다.

이 글에 대한 댓글이 총 9건 있습니다.

WITH apdrscht AS
(
SELECT '센타1' subcenternm, 'A지점' meddeptnm, '가가가' meddrnm, 2 dday, 'A' ampm FROM dual
UNION ALL SELECT '센타1', 'A지점', '나나나', 2, 'A' FROM dual
UNION ALL SELECT '센타1', 'A지점', '다다다', 2, 'P' FROM dual
UNION ALL SELECT '센타1', 'A지점', '차차차', 4, 'P' FROM dual
UNION ALL SELECT '센타1', 'B지점', '라라라', 2, 'A' FROM dual
UNION ALL SELECT '센타1', 'B지점', '마마마', 4, 'P' FROM dual
UNION ALL SELECT '센타2', 'A지점', '바바바', 2, 'P' FROM dual
UNION ALL SELECT '센타2', 'A지점', '사사사', 3, 'A' FROM dual
UNION ALL SELECT '센타2', 'A지점', '아아아', 4, 'P' FROM dual
UNION ALL SELECT '센타2', 'B지점', '자자자', 3, 'P' FROM dual
)
SELECT tt
     , MIN(DECODE(da, '2A', meddrnm)) w01
     , MIN(DECODE(da, '2P', meddrnm)) w02
     , MIN(DECODE(da, '3A', meddrnm)) w03
     , MIN(DECODE(da, '3P', meddrnm)) w04
     , MIN(DECODE(da, '4A', meddrnm)) w05
     , MIN(DECODE(da, '4P', meddrnm)) w06
     , MIN(DECODE(da, '5A', meddrnm)) w07
     , MIN(DECODE(da, '5P', meddrnm)) w08
     , MIN(DECODE(da, '6A', meddrnm)) w09
     , MIN(DECODE(da, '6P', meddrnm)) w10
     , MIN(DECODE(da, '7A', meddrnm)) w11
     , MIN(DECODE(da, '7P', meddrnm)) w12
FROM (SELECT subcenternm ||'-'|| meddeptnm tt
             , dday || ampm da
             , meddrnm
             , ROW_NUMBER() OVER(
               PARTITION BY subcenternm, meddeptnm, dday, ampm
               ORDER BY meddrnm) rn
          FROM apdrscht
        )
GROUP BY tt, rn
ORDER BY tt, rn
;

마농(manon94)님이 2012-10-24 00:11에 작성한 댓글입니다.

11G 라면 PIVOT 구문도 가능합니다.
단, With 처럼 3Tier 에서 동작될지는 모르겠네요.
SELECT *
FROM (SELECT subcenternm ||'-'|| meddeptnm tt
             , dday || ampm da
             , meddrnm
             , ROW_NUMBER() OVER(
               PARTITION BY subcenternm, meddeptnm, dday, ampm
               ORDER BY meddrnm) rn
          FROM apdrscht
        )
PIVOT (MIN(meddrnm) FOR da IN ( '2A' w01
                               , '2P' w02
                               , '3A' w03
                               , '3P' w04
                               , '4A' w05
                               , '4P' w06
                               , '5A' w07
                               , '5P' w08
                               , '6A' w09
                               , '6P' w10
                               , '7A' w11
                               , '7P' w12
                               ) )
ORDER BY tt, rn
;

마농(manon94)님이 2012-10-24 00:25에 작성한 댓글입니다.

감사합니다. 마농님
이 늦은 시간에 답글을 달아주시니... 문제의 해결을 떠나 이렇게 시간을 할애하여 답변을 달아주시는게
결코 싶지 않은데 바로 옆에 계시면 술이라도 사드리고 싶은 마음입니다. 정말 고맙습니다.
저도 이문제 때문에 지금까지 전전긍긍하고 있네요. ㅜㅜ
주신 쿼리문을 바탕으로 재작업 하도록 하겠습니다. 먼저 질문 좀 드려도 될까요.
지금까지 이문제를 해결하려고 이런저런 쿼리문을 짜서 이렇게 돌려보고 저렇게 돌려보았는데
max, min 구분으로도 쿼리문을 짰던 적이 있었습니다.

센터코드   지점코드        월/오전(근무)
센타1         a지점             가가가
                                          나나나
                                          다다다

이러한 결과가 나와야 할 경우,
max로 하면 '다다다'만 나오고
min으로 하면 '가가가'만 나오는 경우가 있었습니다. 제발 이 문제가 해결되었으면 하는 바람입니다.
모쪼록 건승하시길 바랍니다.

쩌그노트님이 2012-10-24 00:46에 작성한 댓글입니다.
이 댓글은 2012-10-24 00:47에 마지막으로 수정되었습니다. Edit Ｘ

아아, 마농님 기존에 with as 문의 결과값과 동일한 결과가 나온 것 같습니다. 자료가 많아 일일이

확인을 못해봤지만 몇개센터지점의 데이터가 일치하는 것으로 보아 자료는 정확하게 나온 것 같습니다.

눈물이 나오려고 하네요. 감사의 마음을 어떻게 전해야 할지...

pro*c에서 코딩한 후 일단 컴파일 해보야 겠습니다. 아, 컴파일이 돼야 할텐데...

결과글 올리도록 할게요. 그럼...

쩌그노트님이 2012-10-24 01:09에 작성한 댓글입니다. Edit Ｘ

아, 컴파일 에러네요.Pro*c에서

ROW_NUMBER() OVER(PARTITION BY B.CODENAME, C.DEPTLNM, A.DDAY, A.AMPM ORDER BY D.USERNAME) rn 이 구문을 지원하지 않은 것 같습니다. pivot문도 장담할 수 없겠군요.

pro*c 컴파일러가 오라클 분석함수에 대해 어느정도 제약을 갖고 있는 것 같습니다.

정말 난감 그 자체이군요. 마농님이 밤늦도록 답변을 달아주셨건만....

일단 쿼리문은 제가 실력이 없어서 유보하고 client 단에서 해결해 봐야 할 거 같습니다. 마농님의 노고에 미치지 못해 그저 죄송할 따름입니다. 염치없지만 다음에도 친절한 답변 기대하겠습니다.

쩌그노트님이 2012-10-24 02:50에 작성한 댓글입니다.
이 댓글은 2012-10-24 04:03에 마지막으로 수정되었습니다. Edit Ｘ

Pro*c 컴파일러 버전 문제군요.

두가지 유형의 SQL 을 사용할 수 있는데요.

Static SQL 의 경우엔 컴파일시에 구문체크를 하게 되구요.

Dynamic SQL 의 경우엔 컴파일시에는 구문체크 안하는걸로 압니다.

Dynamic SQL 로 변경이 가능할지 모르겠네요.

한번 시도해 보세요.

마농(manon94)님이 2012-10-24 08:37에 작성한 댓글입니다.
이 댓글은 2012-10-24 08:38에 마지막으로 수정되었습니다.

-- 분석함수 대신 Self 조인을 이용해 보겠습니다.
SELECT tt
     , MIN(DECODE(da, '2A', meddrnm)) w01
     , MIN(DECODE(da, '2P', meddrnm)) w02
     , MIN(DECODE(da, '3A', meddrnm)) w03
     , MIN(DECODE(da, '3P', meddrnm)) w04
     , MIN(DECODE(da, '4A', meddrnm)) w05
     , MIN(DECODE(da, '4P', meddrnm)) w06
     , MIN(DECODE(da, '5A', meddrnm)) w07
     , MIN(DECODE(da, '5P', meddrnm)) w08
     , MIN(DECODE(da, '6A', meddrnm)) w09
     , MIN(DECODE(da, '6P', meddrnm)) w10
     , MIN(DECODE(da, '7A', meddrnm)) w11
     , MIN(DECODE(da, '7P', meddrnm)) w12
FROM (SELECT a.subcenternm ||'-'|| a.meddeptnm tt
             , a.dday || a.ampm da
             , a.meddrnm
             , COUNT(b.dday) + 1 rn
          FROM apdrscht a
             , apdrscht b
         WHERE a.subcenternm = b.subcenternm(+)
           AND a.meddeptnm = b.meddeptnm(+)
           AND a.dday = b.dday(+)
           AND a.ampm = b.ampm(+)
           AND a.meddrnm > b.meddrnm(+)
         GROUP BY a.subcenternm, a.meddeptnm, a.dday, a.ampm, a.meddrnm
        )
GROUP BY tt, rn
ORDER BY tt, rn
;

마농(manon94)님이 2012-10-24 09:01에 작성한 댓글입니다.

혹시나 해서 들어와 봤는데 역시 댓글을 달아 주셨군요. 뭐라 표현해야 할지...

일단 코드 작성하고 자료 확인해 보면 오전 중에는 답이 나올 것 같네요. 마무리 되면 결과 알려드리겠습니다.

쩌그노트님이 2012-10-24 09:56에 작성한 댓글입니다. Edit Ｘ

오오!!! 잘 됩니다. 쿼리 결과값, pro*c 컴파일 모두 이상없이 수행되었습니다.
며칠동안 끙끙 앓아왔던 난제를 이렇게 간단히 처리해 주시는 군요.
바쁘실텐데도 개인시간까지 할애해 주시면서요. 위처럼 저리도 긴 쿼리문을

이렇게 간단히 처리할 수 있다니 한편으로는 부끄럽고 놀라울 따름입니다.
한마디로 감동 그 자체군요. 이건 경험해 보지 못한 사람이라면 알 수 없을 듯...

select 선언절에서
, COUNT(b.dday) + 1 rn 이부분은 아마 죽었다깨나도 생각치 못했을 겁니다.

이건 경험이나 기술적인 부분보다 쿼리의 작동원리를 알아야 가능할 것 같네요.
저처럼 비슷한 고생을 하시는 분들께 좋은 참고자료가 되겠네요.
마농님 식사 맛있게 하시고요, 저처럼 부족한 사람들의 고민을 해결해 주실 수 있도록 늘 건강하세요.
그럼...

쩌그노트님이 2012-10-24 11:27에 작성한 댓글입니다.
이 댓글은 2012-10-24 11:30에 마지막으로 수정되었습니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
39705	MDB(외부 파일) 데이터 -> Oracle DB에 Insert 하는 방법 좀 알려주세요.	김현	2012-10-26	4807
39704	기간 중복 방지에 대한 해결책을 문의 합니다.	김준기	2012-10-25	4553
39702	PERCENTILE_CONT 함수 사용법 문의 드립니다.	이종석	2012-10-25	4135
39701	마농님 아래 쿼리문 올립니다. [9]	조성훈/쩌그노트	2012-10-23	6158
39700	햇살한조각님 아래 답변에서 혹시라도... [2]	쩌그노트	2012-10-23	4568
39699	with as문을 select 문으로 바꾸려면??? [2]	조성훈	2012-10-23	5129
39697	쿼리 질문 드립니다. [1]	김영준	2012-10-22	4163

작업시간: 0.017초, 이곳 서비스는
PostgreSQL v16.2로 자료를 관리합니다