데이터베이스 질문과 답변
with tst as ( select 'A' product, '17/10/12' dateIf , 3 QTY, 'IN' flag from dual union all select 'A' product, '17/10/12' dateIf , 1 QTY, 'OUT' flag from dual union all select 'A' product, '17/10/12' dateIf , 2 QTY, 'WIP' flag from dual union all select 'A' product, '17/10/13' dateIf , 5 QTY, 'IN' flag from dual union all select 'A' product, '17/10/14' dateIf , 3 QTY, 'IN' flag from dual union all select 'B' product, '17/10/12' dateIf , 3 QTY, 'IN' flag from dual union all select 'B' product, '17/10/13' dateIf , 3 QTY, 'WIP' flag from dual union all select 'C' product, '17/10/13' dateIf , 3 QTY, 'WIP' flag from dual ) ;
위 데이터를 product flag 17/10/12 17/10/13 17/10/14 A IN 3 5 3 A OUT 1 0 0 A WIP 2 0 0 B IN 3 0 0 B OUT 0 0 0 B WIP 0 3 0 C IN 0 0 0 C OUT 0 0 0 C WIP 0 3 0 으로 만들고 싶습니다. 혹시 방법 없나요? ex) 해당 QTY 가 없을 때 0 즉 데이터가 없을때 Flag 는 항상 IN,OUT,WIP 순으로 데이터 없어도 나오게 ex) product c 인경우 데이터는 한개지만 IN,OUT,나오게
by 마농
[2017.12.14 08:47:51]
WITH tst AS
(
SELECT 'A' product, '17/10/12' dateIf, 3 qty, 'IN' flag FROM dual
UNION ALL SELECT 'A', '17/10/12', 1, 'OUT' FROM dual
UNION ALL SELECT 'A', '17/10/12', 2, 'WIP' FROM dual
UNION ALL SELECT 'A', '17/10/13', 5, 'IN' FROM dual
UNION ALL SELECT 'A', '17/10/14', 3, 'IN' FROM dual
UNION ALL SELECT 'B', '17/10/12', 3, 'IN' FROM dual
UNION ALL SELECT 'B', '17/10/13', 3, 'WIP' FROM dual
UNION ALL SELECT 'C', '17/10/13', 3, 'WIP' FROM dual
)
, cod AS
(
SELECT 'IN' flag FROM dual
UNION ALL SELECT 'OUT' FROM dual
UNION ALL SELECT 'WIP' FROM dual
)
SELECT product
, flag
, NVL("17/10/12", 0) "17/10/12"
, NVL("17/10/13", 0) "17/10/13"
, NVL("17/10/14", 0) "17/10/14"
FROM (SELECT b.product
, a.flag
, b.dateif
, b.qty
FROM cod a
LEFT OUTER JOIN tst b
PARTITION BY (b.product)
ON a.flag = b.flag
)
PIVOT (SUM(qty) FOR dateif IN ( '17/10/12' "17/10/12"
, '17/10/13' "17/10/13"
, '17/10/14' "17/10/14"
) )
ORDER BY product, flag
;
by 후이즈
[2017.12.14 08:55:07]
ㅜㅜ 정말 감사합니다. 마농님
피봇은 어찌어찌되는데 저놈의 IN,OUT,WIP 없는 데이터도 나오게끔 하는게 답이 안나왔어요
정말 고맙습니다.
by 우리집아찌
[2017.12.14 09:12:51]
with tst as
(select 'A' product, '17/10/12' dateIf , 3 QTY, 'IN' flag from dual union all
select 'A' product, '17/10/12' dateIf , 1 QTY, 'OUT' flag from dual union all
select 'A' product, '17/10/12' dateIf , 2 QTY, 'WIP' flag from dual union all
select 'A' product, '17/10/13' dateIf , 5 QTY, 'IN' flag from dual union all
select 'A' product, '17/10/14' dateIf , 3 QTY, 'IN' flag from dual union all
select 'B' product, '17/10/12' dateIf , 3 QTY, 'IN' flag from dual union all
select 'B' product, '17/10/13' dateIf , 3 QTY, 'WIP' flag from dual union all
select 'C' product, '17/10/13' dateIf , 3 QTY, 'WIP' flag from dual
) , tst2 AS (
SELECT flag FROM tst GROUP BY flag
)
SELECT b.product
, a.flag
, NVL(b.date1,0) "17/10/12"
, NVL(b.date2,0) "17/10/13"
, NVL(b.date3,0) "17/10/14"
FROM tst2 A
LEFT OUTER JOIN
(SELECT *
FROM tst
PIVOT ( SUM(QTY) FOR dateIf IN ( '17/10/12' date1 , '17/10/13' date2 , '17/10/14' date3))
) B
PARTITION BY (B.product )
ON A.flag = B.flag
ORDER BY b.product , a.flag
by 마농
[2017.12.14 09:24:17]
아하!
아찌님 처럼 피벗을 먼저 하면 서브쿼리가 줄겠네요.
WITH tst AS
(
SELECT 'A' product, '17/10/12' dateIf, 3 qty, 'IN' flag FROM dual
UNION ALL SELECT 'A', '17/10/12', 1, 'OUT' FROM dual
UNION ALL SELECT 'A', '17/10/12', 2, 'WIP' FROM dual
UNION ALL SELECT 'A', '17/10/13', 5, 'IN' FROM dual
UNION ALL SELECT 'A', '17/10/14', 3, 'IN' FROM dual
UNION ALL SELECT 'B', '17/10/12', 3, 'IN' FROM dual
UNION ALL SELECT 'B', '17/10/13', 3, 'WIP' FROM dual
UNION ALL SELECT 'C', '17/10/13', 3, 'WIP' FROM dual
)
, cod AS
(
SELECT 'IN' flag FROM dual
UNION ALL SELECT 'OUT' FROM dual
UNION ALL SELECT 'WIP' FROM dual
)
SELECT b.product
, a.flag
, NVL("17/10/12", 0) "17/10/12"
, NVL("17/10/13", 0) "17/10/13"
, NVL("17/10/14", 0) "17/10/14"
FROM cod a
LEFT OUTER JOIN tst
PIVOT (SUM(qty) FOR dateif IN ( '17/10/12' "17/10/12"
, '17/10/13' "17/10/13"
, '17/10/14' "17/10/14"
) ) b
PARTITION BY (b.product)
ON a.flag = b.flag
ORDER BY b.product, a.flag
;
by 우리집아찌
[2017.12.14 10:16:02]
^^*
by 고수가되고싶어요
[2017.12.14 11:32:58]
아...파티션조인 예전에 문제풀이에서 봤었는데 까먹엇네요
나도잘하고싶다아아아아아
) 버튼을 클릭하여 작성 하시면 됩니다.