database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41088 게시물 읽기
No. 41088
SQL SERVER 2012에서의 PIVOT을 ORACLE11g에서 어떻게 변환 하나요??
작성자
Redical
작성일
2016-02-25 12:04
조회수
9,958

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

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
7648
41090햐..아이디어가 전혀 생각나지 않아서..도움 부탁드립니다. [3]
우태열
2016-02-27
8385
41089오라클 접속 속도 문제 [1]
홍길동
2016-02-26
8297
41088SQL SERVER 2012에서의 PIVOT을 ORACLE11g에서 어떻게 변환 하나요?? [4]
Redical
2016-02-25
9958
41087수정1차) SP 선언시 매개변수를 데이터셋으로 받아서 처리 가능여부? [2]
권순환
2016-02-25
8632
41086pl/sql 에서 dbms_output.put_line 의 성능 문제는 없을까요?
나그네
2016-02-25
7517
41085멀티스레드 분산 select [2]
민주가인
2016-02-25
7969
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.019초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다