데이터베이스 질문과 답변

sql 문의 드립니다. 1 5 834

by zzhozang [SQL Query] [2018.06.21 10:23:53]

안녕하세요.

아래와 같은 조회 결과 테이블이 있을때

WITH T1 ( PLANT, CUSTOMER, PART, PLAN_DATE, PLANT_QTY, IMPORT_DATE, IMPORT_QTY, PROC_DATE, PROC_QTY, LOSS_DATE, LOSS_QTY) AS
(
  SELECT 'PLANT' AS PLANT,'CUSTOMER1' AS CUSTOMER,'PART1' AS PART,'20180101' AS PLAN_DATE,100 AS PLANT_QTY,'20180102' AS IMPORT_DATE,200 AS IMPORT_QTY,'20180103' AS PROC_DATE,300 AS PROC_QTY,'20180103' AS LOSS_DATE,10 AS LOSS_QTY FROM DUAL UNION ALL
  SELECT 'PLANT','CUSTOMER1','PART2','20180111',100,'20180102',200,'20180103',300,'20180103',10 FROM DUAL UNION ALL
  SELECT 'PLANT','CUSTOMER2','PART3','20180121',100,'20180110',200,'20180103',300,'20180103',10 FROM DUAL UNION ALL
  SELECT 'PLANT','CUSTOMER3','PART4','20180131',100,'20180112',200,'20180103',300,'20180103',10 FROM DUAL
)
SELECT *
FROM T1

PLANT CUSTOMER PART PLAN_DATE PLANT_QTY IMPORT_DATE IMPORT_QTY PROC_DATE PROC_QTY LOSS_DATE LOSS_QTY
PLANT CUSTOMER1 PART1 20180101 100 20180102 200 20180103 300 20180103 10
PLANT CUSTOMER1 PART2 20180111 100 20180102 200 20180103 300 20180103 10
PLANT CUSTOMER2 PART3 20180121 100 20180110 200 20180103 300 20180103 10
PLANT CUSTOMER3 PART4 20180131 100 20180112 200 20180103 300 20180103 10

아래와 같이 조회 되도록 할 수 있을지 문의드립니다.

PLANT CUSTOMER PART DATA DATE QTY
PLANT CUSTOMER1 PART1 PLAN 20180101 100
PLANT CUSTOMER1 PART1 IMPORT 20180102 200
PLANT CUSTOMER1 PART1 PROC 20180103 300
PLANT CUSTOMER1 PART1 LOSS 20180103 10
PLANT CUSTOMER1 PART2 PLAN 20180111 100
PLANT CUSTOMER1 PART2 IMPORT 20180102 200
PLANT CUSTOMER1 PART2 PROC 20180103 300
PLANT CUSTOMER1 PART2 LOSS 20180103 10
PLANT CUSTOMER2 PART3 PLAN 20180121 100
PLANT CUSTOMER2 PART3 IMPORT 20180110 200
PLANT CUSTOMER2 PART3 PROC 20180103 300
PLANT CUSTOMER2 PART3 LOSS 20180103 10
PLANT CUSTOMER3 PART4 PLAN 20180131 100
PLANT CUSTOMER3 PART4 IMPORT 20180112 200
PLANT CUSTOMER3 PART4 PROC 20180103 300
PLANT CUSTOMER3 PART4 LOSS 20180103 10

 

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

감사드립니다.

 

by 우리집아찌 [2018.06.21 10:57:04]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
WITH T1 ( PLANT, CUSTOMER, PART, PLAN_DATE, PLANT_QTY, IMPORT_DATE, IMPORT_QTY, PROC_DATE, PROC_QTY, LOSS_DATE, LOSS_QTY) AS
(
  SELECT 'PLANT' AS PLANT,'CUSTOMER1' AS CUSTOMER,'PART1' AS PART,'20180101' AS PLAN_DATE,100 AS PLANT_QTY,'20180102' AS IMPORT_DATE,200 AS IMPORT_QTY,'20180103' AS PROC_DATE,300 AS PROC_QTY,'20180103' AS LOSS_DATE,10 AS LOSS_QTY FROM DUAL UNION ALL
  SELECT 'PLANT','CUSTOMER1','PART2','20180111',100,'20180102',200,'20180103',300,'20180103',10 FROM DUAL UNION ALL
  SELECT 'PLANT','CUSTOMER2','PART3','20180121',100,'20180110',200,'20180103',300,'20180103',10 FROM DUAL UNION ALL
  SELECT 'PLANT','CUSTOMER3','PART4','20180131',100,'20180112',200,'20180103',300,'20180103',10 FROM DUAL
)
 
-- 행복제 이용
SELECT A.PLANT
     , A.CUSTOMER
     , A.PART
     , CASE WHEN LV = 1 THEN 'PLAN'
            WHEN LV = 2 THEN 'IMPORT'
            WHEN LV = 3 THEN 'PROC'
            WHEN LV = 4 THEN 'LOSS'
        END AS DATA    
     , CASE WHEN B.LV = 1 THEN PLAN_DATE
            WHEN B.LV = 2 THEN IMPORT_DATE
            WHEN B.LV = 3 THEN PROC_DATE
            WHEN B.LV = 4 THEN LOSS_DATE
        END AS DATE1      
     , CASE WHEN B.LV = 1 THEN PLANT_QTY
            WHEN B.LV = 2 THEN IMPORT_QTY
            WHEN B.LV = 3 THEN PROC_QTY
            WHEN B.LV = 4 THEN LOSS_QTY
        END AS QTY           
  FROM T1 A
     , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 4 ) B
  ORDER BY A.PLANT , A.CUSTOMER , A.PART , B.LV

 

by 신이만든지기 [2018.06.21 11:22:32]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- oracle 11g 이상에서  unpiovot이용하는 방법
SELECT PLANT
     , CUSTOMER
     , PART
     , DATA
     , DATE1
     , QTY
  FROM T1
       UNPIVOT ((QTY, DATE1)
               FOR DATA
               IN ((PLANT_QTY, PLAN_DATE) AS 'PLAN'
                , (IMPORT_QTY, IMPORT_DATE) AS 'IMPORT'
                , (PROC_QTY, PROC_DATE) AS 'PROC'
                , (LOSS_QTY, LOSS_DATE) AS 'LOSS'));

 

by 마농 [2018.06.21 11:25:36]
?
1
2
3
4
5
6
7
8
SELECT *
  FROM t1
 UNPIVOT (("DATE", qty) FOR data IN ( (  plan_date,  plant_qty) AS 'PLAN'
                                    , (import_date, import_qty) AS 'IMPORT'
                                    , (  proc_date,   proc_qty) AS 'PROC'
                                    , (  loss_date,   loss_qty) AS 'LOSS'
                                    ) )
;

 

by zzhozang [2018.06.21 11:35:01]

답변정말감사드립니다.

by 아발란체 [2018.06.21 13:06:04]

다들 멋지심 ~~~~~ ★.★)/

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