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

Oracle Q&A 41088 게시물 읽기

No. 41088

SQL SERVER 2012에서의 PIVOT을 ORACLE11g에서 어떻게 변환 하나요??
작성자	Redical	작성일	2016-02-25 12:04	조회수	9,877

항상 많은 도움 얻고 있습니다.

SQL SERVER에서 ORACLE11g로 마이그레이션 중 입니다.

SELECT MECHCOD, '1. 계획 수량' AS PART,

NVL(D01,0) D01, NVL(D02,0) D02, NVL(D03,0) D03, NVL(D04,0) D05, NVL(D05,0) D05,

NVL(D06,0) D06, NVL(D07,0) D07, NVL(D08,0) D08, NVL(D09,0) D09, NVL(D10,0) D10,

NVL(D11,0) D11, NVL(D12,0) D12, NVL(D13,0) D13, NVL(D14,0) D14, NVL(D15,0) D15,

NVL(D16,0) D16, NVL(D17,0) D17, NVL(D18,0) D18, NVL(D19,0) D19, NVL(D20,0) D20,

NVL(D21,0) D21, NVL(D22,0) D22, NVL(D23,0) D23, NVL(D24,0) D24, NVL(D25,0) D25,

NVL(D26,0) D26, NVL(D27,0) D27, NVL(D28,0) D28, NVL(D29,0) D29, NVL(D30,0) D30,

NVL(D31,0) D31,

NVL(D01,0)+NVL(D02,0)+NVL(D03,0)+NVL(D04,0)+NVL(D05,0)+

NVL(D06,0)+NVL(D07,0)+NVL(D08,0)+NVL(D09,0)+NVL(D10,0)+

NVL(D11,0)+NVL(D12,0)+NVL(D13,0)+NVL(D14,0)+NVL(D15,0)+

NVL(D16,0)+NVL(D17,0)+NVL(D18,0)+NVL(D19,0)+NVL(D20,0)+

NVL(D21,0)+NVL(D22,0)+NVL(D23,0)+NVL(D24,0)+NVL(D25,0)+

NVL(D26,0)+NVL(D27,0)+NVL(D28,0)+NVL(D29,0)+NVL(D30,0)+NVL(D31,0) AS TOT,

(NVL(D01,0)+NVL(D02,0)+NVL(D03,0)+NVL(D04,0)+NVL(D05,0)+

NVL(D06,0)+NVL(D07,0)+NVL(D08,0)+NVL(D09,0)+NVL(D10,0)+

NVL(D11,0)+NVL(D12,0)+NVL(D13,0)+NVL(D14,0)+NVL(D15,0)+

NVL(D16,0)+NVL(D17,0)+NVL(D18,0)+NVL(D19,0)+NVL(D20,0)+

NVL(D21,0)+NVL(D22,0)+NVL(D23,0)+NVL(D24,0)+NVL(D25,0)+

NVL(D26,0)+NVL(D27,0)+NVL(D28,0)+NVL(D29,0)+NVL(D30,0)+NVL(D31,0) ) / 12 AS AVE

FROM

(SELECT MECHCOD, 'D' || SUBSTR(PRODATE,7,2) AS DD,

CASE WHEN TOT_TIME > 0 THEN

ROUND((CAST(TOT_TIME AS FLOAT) / MCT * MCAVITY),0)

ELSE 0

END AS PLAN_QTY,

CASE WHEN fnTotTime(MECHCOD,PRODATE,PRODATE) > 0 THEN

ROUND((CAST(ACT_TIME AS FLOAT) / MCT * MCAVITY),0)

ELSE 0

END AS ACT_QTY

FROM VTotTime

WHERE PRODATE BETWEEN '20160101' and '20160131' --@Fdate AND @Tdate

) PV

PIVOT ( SUM(PV.PLAN_QTY) FOR PV.DD IN (D01,D02,D03,D04,D05,D06,D07,D08,D09,D10,D11,D12,D13,D14,D15,D16,

D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31) ) AS PVT

PIVOT문에서 ORA-01748: 열명 그 자체만 사용할 수 있습니다. 라는 오류가 납니다.

'D' || SUBSTR(PRODATE,7,2) AS DD, 필드에서 오류가 나는거 같은데 ORACLE11g에서는 어떻게 구현 하나요???

도움 부탁 드립니다..

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

IN 절안에 들어갈 내용은 D01, D02 가 아니라 'D01', 'D02' 이죠.
대신 위에서 D01 을 컬럼명으로 사용하려면 'D01' D01, 'D02' D02 처럼 알리아스 줘야합니다.
그런데...
PLAN_QTY 와 ACT_QTY 가 있는데 하나만 SUM 한 것도 이상하구요.
작성 실패한 ORACLE 쿼리 말고
잘 돌아가던 MSSQL 쿼리를 보여주세요.

마농(manon94)님이 2016-02-25 13:16에 작성한 댓글입니다.

마농님 고맙습니다.

SQL SERVER의 원문 입니다

SELECT MECHCOD, '1. 계획 수량' AS PART,

ISNULL(D01,0), ISNULL(D02,0), ISNULL(D03,0), ISNULL(D04,0), ISNULL(D05,0),

ISNULL(D06,0), ISNULL(D07,0), ISNULL(D08,0), ISNULL(D09,0), ISNULL(D10,0),

ISNULL(D11,0), ISNULL(D12,0), ISNULL(D13,0), ISNULL(D14,0), ISNULL(D15,0),

ISNULL(D16,0), ISNULL(D17,0), ISNULL(D18,0), ISNULL(D19,0), ISNULL(D20,0),

ISNULL(D21,0), ISNULL(D22,0), ISNULL(D23,0), ISNULL(D24,0), ISNULL(D25,0),

ISNULL(D26,0), ISNULL(D27,0), ISNULL(D28,0), ISNULL(D29,0), ISNULL(D30,0),

ISNULL(D31,0),

ISNULL(D01,0)+ISNULL(D02,0)+ISNULL(D03,0)+ISNULL(D04,0)+ISNULL(D05,0)+

ISNULL(D06,0)+ISNULL(D07,0)+ISNULL(D08,0)+ISNULL(D09,0)+ISNULL(D10,0)+

ISNULL(D11,0)+ISNULL(D12,0)+ISNULL(D13,0)+ISNULL(D14,0)+ISNULL(D15,0)+

ISNULL(D16,0)+ISNULL(D17,0)+ISNULL(D18,0)+ISNULL(D19,0)+ISNULL(D20,0)+

ISNULL(D21,0)+ISNULL(D22,0)+ISNULL(D23,0)+ISNULL(D24,0)+ISNULL(D25,0)+

ISNULL(D26,0)+ISNULL(D27,0)+ISNULL(D28,0)+ISNULL(D29,0)+ISNULL(D30,0)+ISNULL(D31,0) AS TOT,

(ISNULL(D01,0)+ISNULL(D02,0)+ISNULL(D03,0)+ISNULL(D04,0)+ISNULL(D05,0)+

ISNULL(D06,0)+ISNULL(D07,0)+ISNULL(D08,0)+ISNULL(D09,0)+ISNULL(D10,0)+

ISNULL(D11,0)+ISNULL(D12,0)+ISNULL(D13,0)+ISNULL(D14,0)+ISNULL(D15,0)+

ISNULL(D16,0)+ISNULL(D17,0)+ISNULL(D18,0)+ISNULL(D19,0)+ISNULL(D20,0)+

ISNULL(D21,0)+ISNULL(D22,0)+ISNULL(D23,0)+ISNULL(D24,0)+ISNULL(D25,0)+

ISNULL(D26,0)+ISNULL(D27,0)+ISNULL(D28,0)+ISNULL(D29,0)+ISNULL(D30,0)+ISNULL(D31,0) ) / 12 AS AVE

FROM

(SELECT MECHCOD, 'D'+ SUBSTRING(PRODATE,7,2) AS DD,

CASE WHEN @Option = 'E' AND TOT_TIME > 0 THEN

ROUND((CAST(TOT_TIME AS FLOAT) / MCT * MCAVITY),0)

WHEN @Option = 'T' AND dbo.fnTotTime(MECHCOD,PRODATE,PRODATE) > 0 THEN

ROUND((CAST(ACT_TIME AS FLOAT) / MCT * MCAVITY),0)

ELSE 0

END AS PLAN_QTY

FROM VTotTime

WHERE PRODATE BETWEEN @Fdate AND @Tdate

) PV

PIVOT ( SUM(PV.PLAN_QTY) FOR PV.DD IN (D01,D02,D03,D04,D05,D06,D07,D08,D09,D10,D11,D12,D13,D14,D15,D16,

D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31) ) AS PVT

Redical님이 2016-02-25 13:30에 작성한 댓글입니다. Edit Ｘ

SELECT *
FROM (SELECT mechcod
             , SUBSTRING(prodate, 7, 2) AS dd
             , CASE WHEN :Option = 'E' AND tot_time > 0
                    THEN ROUND(tot_time / mct * mcavity, 0)
                    WHEN :Option = 'T'
                     AND fnTotTime(mechcod, prodate, prodate) > 0
                    THEN ROUND(act_time / mct * mcavity, 0)
                    ELSE 0
                END AS plan_qty
          FROM VTotTime
         WHERE prodate BETWEEN :Fdate AND :Tdate
        ) pv
PIVOT (SUM(plan_qty) FOR
        dd IN ( '01' D01, '02' D02, '03' D03, '04' D04, '05' D05
              , '06' D06, '07' D07, '08' D08, '09' D09, '10' D10
              , '11' D11, '12' D12, '13' D13, '14' D14, '15' D15
              , '16' D16, '17' D17, '18' D18, '19' D19, '20' D20
              , '21' D21, '22' D22, '23' D23, '24' D24, '25' D25
              , '26' D26, '27' D27, '28' D28, '29' D29, '30' D30
              , '31' D31 )
        ) pvt
ORDER BY mechcod
;

PIVOT 구문만 그대로 옮기면 위와 같습니다.
그러나, 피벗 이후 tot 나 ave 를 구하고 NVL 처리를 하는 과정이
너무나도 구문이 길고 복잡해 지네요.
이럴거면 차라리 PIVOT 구문 안쓰는게 더 간결할 듯 하네요.

SELECT mechcod
     , NVL(MIN(DECODE(dd, '01', plan_qty)), 0) d01
     , NVL(MIN(DECODE(dd, '02', plan_qty)), 0) d02
     , NVL(MIN(DECODE(dd, '03', plan_qty)), 0) d03
     , NVL(MIN(DECODE(dd, '04', plan_qty)), 0) d04
     , NVL(MIN(DECODE(dd, '05', plan_qty)), 0) d05
     , NVL(MIN(DECODE(dd, '06', plan_qty)), 0) d06
     , NVL(MIN(DECODE(dd, '07', plan_qty)), 0) d07
     , NVL(MIN(DECODE(dd, '08', plan_qty)), 0) d08
     , NVL(MIN(DECODE(dd, '09', plan_qty)), 0) d09
     , NVL(MIN(DECODE(dd, '10', plan_qty)), 0) d10
     , NVL(MIN(DECODE(dd, '11', plan_qty)), 0) d11
     , NVL(MIN(DECODE(dd, '12', plan_qty)), 0) d12
     , NVL(MIN(DECODE(dd, '13', plan_qty)), 0) d13
     , NVL(MIN(DECODE(dd, '14', plan_qty)), 0) d14
     , NVL(MIN(DECODE(dd, '15', plan_qty)), 0) d15
     , NVL(MIN(DECODE(dd, '16', plan_qty)), 0) d16
     , NVL(MIN(DECODE(dd, '17', plan_qty)), 0) d17
     , NVL(MIN(DECODE(dd, '18', plan_qty)), 0) d18
     , NVL(MIN(DECODE(dd, '19', plan_qty)), 0) d19
     , NVL(MIN(DECODE(dd, '20', plan_qty)), 0) d20
     , NVL(MIN(DECODE(dd, '21', plan_qty)), 0) d21
     , NVL(MIN(DECODE(dd, '22', plan_qty)), 0) d22
     , NVL(MIN(DECODE(dd, '23', plan_qty)), 0) d23
     , NVL(MIN(DECODE(dd, '24', plan_qty)), 0) d24
     , NVL(MIN(DECODE(dd, '25', plan_qty)), 0) d25
     , NVL(MIN(DECODE(dd, '26', plan_qty)), 0) d26
     , NVL(MIN(DECODE(dd, '27', plan_qty)), 0) d27
     , NVL(MIN(DECODE(dd, '28', plan_qty)), 0) d28
     , NVL(MIN(DECODE(dd, '29', plan_qty)), 0) d29
     , NVL(MIN(DECODE(dd, '30', plan_qty)), 0) d30
     , NVL(MIN(DECODE(dd, '31', plan_qty)), 0) d31
     , NVL(SUM(plan_qty), 0) tot
     , NVL(SUM(plan_qty), 0) / 12 ave
FROM (SELECT mechcod
             , SUBSTRING(prodate, 7, 2) AS dd
             , SUM(
               CASE WHEN :Option = 'E' AND tot_time > 0
                    THEN ROUND(tot_time / mct * mcavity, 0)
                    WHEN :Option = 'T'
                     AND fnTotTime(mechcod, prodate, prodate) > 0
                    THEN ROUND(act_time / mct * mcavity, 0)
                    ELSE 0
                END
               ) AS plan_qty
          FROM VTotTime
         WHERE prodate BETWEEN :Fdate AND :Tdate
         GROUP BY mechcod, SUBSTRING(prodate, 7, 2)
        ) pv
GROUP BY mechcod
ORDER BY mechcod
;

마농(manon94)님이 2016-02-25 14:02에 작성한 댓글입니다.
이 댓글은 2016-02-25 14:04에 마지막으로 수정되었습니다.

마농님 대단히 감사합니다.

많은 도움이 되었습니다.

좋은 하루 되세요...

Redical님이 2016-02-25 14:09에 작성한 댓글입니다.
이 댓글은 2016-02-25 17:24에 마지막으로 수정되었습니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
41091	리버스 엔지니어링 개념 관련 질문입니다. [1]	조동건	2016-02-28	7587
41090	햐..아이디어가 전혀 생각나지 않아서..도움 부탁드립니다. [3]	우태열	2016-02-27	8323
41089	오라클 접속 속도 문제 [1]	홍길동	2016-02-26	8234
41088	SQL SERVER 2012에서의 PIVOT을 ORACLE11g에서 어떻게 변환 하나요?? [4]	Redical	2016-02-25	9877
41087	수정1차) SP 선언시 매개변수를 데이터셋으로 받아서 처리 가능여부? [2]	권순환	2016-02-25	8561
41086	pl/sql 에서 dbms_output.put_line 의 성능 문제는 없을까요?	나그네	2016-02-25	7462
41085	멀티스레드 분산 select [2]	민주가인	2016-02-25	7901