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 41197 게시물 읽기
No. 41197
오라클 쿼리문 SQL SERVER로 바꾸려면??
작성자
헤매이다
작성일
2016-07-18 18:33
조회수
6,987

오라클에서는 어찌하다 쿼리를 작성 했습니다.

SQL SERVER에서 UNPIVOT 기능을 해결해야 하는데...

마이그레이션 할 일들이 많아서인지 잘 안되네요..

고수님들께 도움을 요청 드립니다.

 

WITH N AS

(SELECT '0' AS idno

, m01, m02, m03, m04, m05, m06, m07, m08, m09, m10, m11, m12

, 0 AS tot

,(m01+ m02+ m03+ m04+ m05+ m06+ m07+ m08+ m09+ m10+ m11+ m12) / 12 AS ave

FROM pranms01

WHERE m_year = '2015'

AND mgubun = 'AB'

)

--

SELECT *

FROM

(SELECT m.mechcod, f.mechseq, f.mechnam

, ' 1. 생산 수량' AS pid

, m.pm01, m.pm02, m.pm03, m.pm04, m.pm05, m.pm06, m.pm07, m.pm08

, m.pm09, m.pm10, m.pm11, m.pm12, m.ptot, m.pave

, ' 2. 불량 수량' AS bid

, m.bm01, m.bm02, m.bm03, m.bm04, m.bm05, m.bm06, m.bm07, m.bm08

, m.bm09, m.bm10, m.bm11, m.bm12, m.btot, m.bave

, ' 3. 불량 PPM' AS mid

, m.pp01, m.pp02, m.pp03, m.pp04, m.pp05, m.pp06, m.pp07, m.pp08

, m.pp09, m.pp10, m.pp11, m.pp12, m.pptot, m.ppave

, ' 4. 달성율(%)' AS rid

, ROUND( (CASE WHEN m.pp01 > 0 AND m.nm01 > 0 THEN m.pp01 / m.nm01 * 100 ELSE 0 END),0) rm01

, ROUND( (CASE WHEN m.pp02 > 0 AND m.nm02 > 0 THEN m.pp02 / m.nm02 * 100 ELSE 0 END),0) rm02

, ROUND( (CASE WHEN m.pp03 > 0 AND m.nm03 > 0 THEN m.pp03 / m.nm03 * 100 ELSE 0 END),0) rm03

, ROUND( (CASE WHEN m.pp04 > 0 AND m.nm04 > 0 THEN m.pp04 / m.nm04 * 100 ELSE 0 END),0) rm04

, ROUND( (CASE WHEN m.pp05 > 0 AND m.nm05 > 0 THEN m.pp05 / m.nm05 * 100 ELSE 0 END),0) rm05

, ROUND( (CASE WHEN m.pp06 > 0 AND m.nm06 > 0 THEN m.pp06 / m.nm06 * 100 ELSE 0 END),0) rm06

, ROUND( (CASE WHEN m.pp07 > 0 AND m.nm07 > 0 THEN m.pp07 / m.nm07 * 100 ELSE 0 END),0) rm07

, ROUND( (CASE WHEN m.pp08 > 0 AND m.nm08 > 0 THEN m.pp08 / m.nm08 * 100 ELSE 0 END),0) rm08

, ROUND( (CASE WHEN m.pp09 > 0 AND m.nm09 > 0 THEN m.pp09 / m.nm09 * 100 ELSE 0 END),0) rm09

, ROUND( (CASE WHEN m.pp10 > 0 AND m.nm10 > 0 THEN m.pp10 / m.nm10 * 100 ELSE 0 END),0) rm10

, ROUND( (CASE WHEN m.pp11 > 0 AND m.nm11 > 0 THEN m.pp11 / m.nm11 * 100 ELSE 0 END),0) rm11

, ROUND( (CASE WHEN m.pp12 > 0 AND m.nm12 > 0 THEN m.pp12 / m.nm12 * 100 ELSE 0 END),0) rm12

, ROUND( (CASE WHEN m.pptot > 0 AND m.ntot > 0 THEN m.pptot / m.nm01 * 100 ELSE 0 END),0) rtot

, ROUND( (CASE WHEN m.ppave > 0 AND m.nave > 0 THEN m.ppave / m.nm02 * 100 ELSE 0 END),0) rave

FROM (SELECT p.mechcod

, n.m01 AS nm01, n.m02 AS nm02, n.m03 AS nm03, n.m04 AS nm04

, n.m05 AS nm05, n.m06 AS nm06, n.m07 AS nm07, n.m08 AS nm08

, n.m09 AS nm09, n.m10 AS nm10, n.m11 AS nm11, n.m12 AS nm12

, n.tot AS ntot, n.ave AS nave

, p.m01 AS pm01, p.m02 AS pm02, p.m03 AS pm03, p.m04 AS pm04

, p.m05 AS pm05, p.m06 AS pm06, p.m07 AS pm07, p.m08 AS pm08

, p.m09 AS pm09, p.m10 AS pm10, p.m11 AS pm11, p.m12 AS pm12

, p.tot AS ptot, p.ave AS pave

, b.m01 AS bm01, b.m02 AS bm02, b.m03 AS bm03, b.m04 AS bm04

, b.m05 AS bm05, b.m06 AS bm06, b.m07 AS bm07, b.m08 AS bm08

, b.m09 AS bm09, b.m10 AS bm10, b.m11 AS bm11, b.m12 AS bm12

, b.tot AS btot, b.ave AS bave

, ROUND((CASE WHEN b.m01 > 0 AND p.m01 > 0 THEN ROUND((b.m01 / p.m01 * 1000000),2) ELSE 0 END),0) AS pp01

, ROUND((CASE WHEN b.m02 > 0 AND p.m02 > 0 THEN ROUND((b.m02 / p.m02 * 1000000),2) ELSE 0 END),0) AS pp02

, ROUND((CASE WHEN b.m03 > 0 AND p.m03 > 0 THEN ROUND((b.m03 / p.m03 * 1000000),2) ELSE 0 END),0) AS pp03

, ROUND((CASE WHEN b.m04 > 0 AND p.m04 > 0 THEN ROUND((b.m04 / p.m04 * 1000000),2) ELSE 0 END),0) AS pp04

, ROUND((CASE WHEN b.m05 > 0 AND p.m05 > 0 THEN ROUND((b.m05 / p.m05 * 1000000),2) ELSE 0 END),0) AS pp05

, ROUND((CASE WHEN b.m06 > 0 AND p.m06 > 0 THEN ROUND((b.m06 / p.m06 * 1000000),2) ELSE 0 END),0) AS pp06

, ROUND((CASE WHEN b.m07 > 0 AND p.m07 > 0 THEN ROUND((b.m07 / p.m07 * 1000000),2) ELSE 0 END),0) AS pp07

, ROUND((CASE WHEN b.m08 > 0 AND p.m08 > 0 THEN ROUND((b.m08 / p.m08 * 1000000),2) ELSE 0 END),0) AS pp08

, ROUND((CASE WHEN b.m09 > 0 AND p.m09 > 0 THEN ROUND((b.m09 / p.m09 * 1000000),2) ELSE 0 END),0) AS pp09

, ROUND((CASE WHEN b.m10 > 0 AND p.m10 > 0 THEN ROUND((b.m10 / p.m10 * 1000000),2) ELSE 0 END),0) AS pp10

, ROUND((CASE WHEN b.m11 > 0 AND p.m11 > 0 THEN ROUND((b.m11 / p.m11 * 1000000),2) ELSE 0 END),0) AS pp11

, ROUND((CASE WHEN b.m12 > 0 AND p.m12 > 0 THEN ROUND((b.m12 / p.m12 * 1000000),2) ELSE 0 END),0) AS pp12

, ROUND((CASE WHEN b.tot > 0 AND p.tot > 0 THEN ROUND(((b.tot/ p.tot * 1000000) / 12),2) ELSE 0 END),0) AS pptot

, ROUND((CASE WHEN b.ave > 0 AND p.ave > 0 THEN ROUND(((b.ave/ p.ave * 1000000) / 12),2) ELSE 0 END),0) AS ppave

FROM (SELECT p.mechcod

, NVL(SUM(DECODE(mm, '01', tot_qty)), 0) AS m01

, NVL(SUM(DECODE(mm, '02', tot_qty)), 0) AS m02

, NVL(SUM(DECODE(mm, '03', tot_qty)), 0) AS m03

, NVL(SUM(DECODE(mm, '04', tot_qty)), 0) AS m04

, NVL(SUM(DECODE(mm, '05', tot_qty)), 0) AS m05

, NVL(SUM(DECODE(mm, '06', tot_qty)), 0) AS m06

, NVL(SUM(DECODE(mm, '07', tot_qty)), 0) AS m07

, NVL(SUM(DECODE(mm, '08', tot_qty)), 0) AS m08

, NVL(SUM(DECODE(mm, '09', tot_qty)), 0) AS m09

, NVL(SUM(DECODE(mm, '10', tot_qty)), 0) AS m10

, NVL(SUM(DECODE(mm, '11', tot_qty)), 0) AS m11

, NVL(SUM(DECODE(mm, '12', tot_qty)), 0) AS m12

, NVL(SUM(tot_qty), 0) AS tot

, NVL(SUM(ROUND( (tot_qty / 12), 0)),0) AS ave

FROM (SELECT p.mechcod

, SUBSTR(prodate, 5,2) AS mm

, NVL(tot_num,0) AS tot_qty

FROM PRAOTR01 p

LEFT JOIN prfams01 m ON m.mechcod = p.mechcod

WHERE prodate BETWEEN '20150101' and '20151231' --:Fdate AND :Tdate

AND m.use_yn = 'Y'

AND tot_num > 0

) p

GROUP BY p.mechcod

) p

LEFT JOIN

(SELECT b.mechcod

, NVL(SUM(DECODE(mm, '01', bad_qty)), 0) AS m01

, NVL(SUM(DECODE(mm, '02', bad_qty)), 0) AS m02

, NVL(SUM(DECODE(mm, '03', bad_qty)), 0) AS m03

, NVL(SUM(DECODE(mm, '04', bad_qty)), 0) AS m04

, NVL(SUM(DECODE(mm, '05', bad_qty)), 0) AS m05

, NVL(SUM(DECODE(mm, '06', bad_qty)), 0) AS m06

, NVL(SUM(DECODE(mm, '07', bad_qty)), 0) AS m07

, NVL(SUM(DECODE(mm, '08', bad_qty)), 0) AS m08

, NVL(SUM(DECODE(mm, '09', bad_qty)), 0) AS m09

, NVL(SUM(DECODE(mm, '10', bad_qty)), 0) AS m10

, NVL(SUM(DECODE(mm, '11', bad_qty)), 0) AS m11

, NVL(SUM(DECODE(mm, '12', bad_qty)), 0) AS m12

, NVL(SUM(bad_qty), 0) AS tot

, NVL(SUM(ROUND( (bad_qty / 12), 0)),0) AS ave

FROM (SELECT p.mechcod

, SUBSTR(prodate, 5,2) AS mm

, NVL(bad_qty,0) AS bad_qty

FROM praotr04 p

LEFT JOIN prfams01 m ON m.mechcod = p.mechcod

WHERE prodate BETWEEN '20150101' and '20151231' --:Fdate AND :Tdate

AND m.use_yn = 'Y'

AND bad_qty > 0

) b

GROUP BY b.mechcod

) b ON b.mechcod = p.mechcod,

n

) m

LEFT JOIN prfams01 f ON f.mechcod = m.mechcod

)

UNPIVOT ((clause, m01, m02, m03, m04, m05, m06, m07, m08, m09, m10, m11, m12, tot, ave)

FOR seq IN ( (pid, pm01,pm02,pm03,pm04,pm05,pm06,pm07,pm08,pm09,pm10,pm11,pm12,ptot,pave) AS 1

, (bid, bm01,bm02,bm03,bm04,bm05,bm06,bm07,bm08,bm09,bm10,bm11,bm12,btot,bave) AS 2

, (mid, pp01,pp02,pp03,pp04,pp05,pp06,pp07,pp08,pp09,pp10,pp11,pp12,pptot,ppave) AS 3

, (rid, rm01,rm02,rm03,rm04,rm05,rm06,rm07,rm08,rm09,rm10,rm11,rm12,rtot,rave) AS 4

)

)

ORDER BY mechseq, seq

 

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

WITH p AS
(
-- 1. 생산 수량
SELECT mechcod
     , mechseq
     , mechnam
     , COALESCE(mm, gb1, gb2) mm
     , CASE gb2 WHEN 'ave' THEN ROUND(SUM(pm)/12., 2) ELSE SUM(pm) END pm
  FROM (SELECT m.mechcod
             , m.mechseq
             , m.mechnam
             , SUBSTRING(p.prodate, 5, 2) AS mm
             , tot_num AS pm
             , 'tot' gb1
             , 'ave' gb2
          FROM praotr01 p
         INNER JOIN prfams01 m
            ON m.mechcod = p.mechcod
         WHERE p.prodate BETWEEN '20150101' AND '20151231'
           AND m.use_yn = 'Y'
           AND p.tot_num > 0
        ) p
 GROUP BY GROUPING SETS ( (mechcod, mechseq, mechnam, mm )
                        , (mechcod, mechseq, mechnam, gb1)
                        , (mechcod, mechseq, mechnam, gb2)
                        )
)
, b AS
(
-- 2. 불량 수량
SELECT mechcod
     , mechseq
     , mechnam
     , COALESCE(mm, gb1, gb2) mm
     , CASE gb2 WHEN 'ave' THEN ROUND(SUM(bm)/12., 2) ELSE SUM(bm) END bm
  FROM (SELECT m.mechcod
             , m.mechseq
             , m.mechnam
             , SUBSTRING(prodate, 5,2) AS mm
             , p.bad_qty AS bm
             , 'tot' gb1
             , 'ave' gb2
          FROM praotr04 p
         INNER JOIN prfams01 m
            ON m.mechcod = p.mechcod
         WHERE p.prodate BETWEEN '20150101' AND '20151231'
           AND m.use_yn = 'Y'
           AND p.bad_qty > 0
        ) b
 GROUP BY GROUPING SETS ( (mechcod, mechseq, mechnam, mm )
                        , (mechcod, mechseq, mechnam, gb1)
                        , (mechcod, mechseq, mechnam, gb2)
                        )
)
, n AS
(
-- 5. 달성 목표
SELECT  m01,m02,m03,m04,m05,m06,m07,m08,m09,m10,m11,m12
     , (m01+m02+m03+m04+m05+m06+m07+m08+m09+m10+m11+m12)    AS tot
     , (m01+m02+m03+m04+m05+m06+m07+m08+m09+m10+m11+m12)/12 AS ave
  FROM pranms01
 WHERE m_year = '2015'
   AND mgubun = 'AB'
)
SELECT mechcod
     , mechseq
     , mechnam
     , CASE gb WHEN 'pm' THEN 1
               WHEN 'bm' THEN 2
               WHEN 'pp' THEN 3
               WHEN 'rm' THEN 4
                END AS seq
     , CASE gb WHEN 'pm' THEN ' 1. 생산 수량'
               WHEN 'bm' THEN ' 2. 불량 수량'
               WHEN 'pp' THEN ' 3. 불량 PPM'
               WHEN 'rm' THEN ' 4. 달성율(%)'
                END AS clause
     , "01", "02", "03", "04", "05", "06"
     , "07", "08", "09", "10", "11", "12"
     , "tot", "ave"
  FROM (SELECT mechcod
             , mechseq
             , mechnam
             , mm
             , CAST(pm AS DECIMAL(18, 2)) pm
             , CAST(bm AS DECIMAL(18, 2)) bm
             , CAST(1000000. * bm / NULLIF(pm, 0) AS DECIMAL(18, 2)) pp
             , CAST(1000000. * bm / NULLIF(pm, 0)
                            / NULLIF(nm, 0) * 100 AS DECIMAL(18, 2)) rm
          FROM (SELECT NVL(p.mechcod, b.mechcod) mechcod
                     , NVL(p.mechseq, b.mechseq) mechseq
                     , NVL(p.mechnam, b.mechnam) mechnam
                     , NVL(p.mm, b.mm) mm
                     , p.pm
                     , b.bm
                     , CASE NVL(p.mm, b.mm) WHEN '01'  THEN n.m01
                                            WHEN '02'  THEN n.m02
                                            WHEN '03'  THEN n.m03
                                            WHEN '04'  THEN n.m04
                                            WHEN '05'  THEN n.m05
                                            WHEN '06'  THEN n.m06
                                            WHEN '07'  THEN n.m07
                                            WHEN '08'  THEN n.m08
                                            WHEN '09'  THEN n.m09
                                            WHEN '10'  THEN n.m10
                                            WHEN '11'  THEN n.m11
                                            WHEN '12'  THEN n.m12
                                            WHEN 'tot' THEN n.tot
                                            WHEN 'ave' THEN n.ave
                                             END AS nm
                  FROM p
                  FULL OUTER JOIN b
                    ON p.mechcod = b.mechcod
                   AND p.mm      = b.mm
                 CROSS JOIN n
                ) m
        ) a
 UNPIVOT (v FOR gb IN (pm, bm, pp, rm)) a
 PIVOT (MIN(v) FOR mm IN ( "01", "02", "03", "04", "05", "06"
                         , "07", "08", "09", "10", "11", "12"
                         , "tot", "ave"
                         ) ) a
 ORDER BY mechseq, seq
;
 

마농(manon94)님이 2016-07-20 10:30에 작성한 댓글입니다.
이 댓글은 2016-07-20 10:30에 마지막으로 수정되었습니다.

마동님 대단하십니다.

요긴하게 잘 쓰겠습니다.

다시한번 감사드립니다.

헤메이다님이 2016-07-21 13:15에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
41200스키마 확인 [1]
봉달이2
2016-07-22
6667
41199기준일에서 일정한 간격으로 조회하려고 합니다. [2]
선이
2016-07-21
6795
41198초보자입니다.(급하기도 하구요) [7]
신무기
2016-07-19
7765
41197오라클 쿼리문 SQL SERVER로 바꾸려면?? [2]
헤매이다
2016-07-18
6987
41196ㅂㅈㄷㅂㅈ [1]
강컴공
2016-07-12
6901
4119511g se 에 em 설치가 가능한가요?
남영민
2016-07-07
6900
41194실용성 있는 sql 퀴즈입니다. [5]
김흥수
2016-07-07
9821
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.048초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다