데이터베이스 질문과 답변
by 우리집아찌
[2019.01.14 17:31:51]
원하시는 결과값 형태도 알려주세요.
by 우리집아찌
[2019.01.14 17:35:34]
WITH T( A,B,C,D,E,F) AS (
SELECT '사과','서울','Apple', 201901, 150, 1150 FROM DUAL UNION ALL
SELECT '사과','서울','Apple', 201902, 250, 1250 FROM DUAL UNION ALL
SELECT '사과','서울','Apple', 201903, 350, 1350 FROM DUAL UNION ALL
SELECT '딸기','인천','Strawberry', 201901, 450, 1450 FROM DUAL UNION ALL
SELECT '딸기','인천','Strawberry', 201902, 550, 1550 FROM DUAL UNION ALL
SELECT '딸기','인천','Strawberry', 201903, 650, 1650 FROM DUAL UNION ALL
SELECT '포도','목포','grape', 201901, 750, 1750 FROM DUAL UNION ALL
SELECT '포도','목포','grape', 201902, 850, 1850 FROM DUAL UNION ALL
SELECT '포도','목포','grape', 201903, 950, 1950 FROM DUAL UNION ALL
SELECT '감자','강원','potato', 201901, 150, 2050 FROM DUAL UNION ALL
SELECT '감자','강원','potato', 201902, 250, 2150 FROM DUAL UNION ALL
SELECT '감자','강원','potato', 201903, 350, 2250 FROM DUAL
)
-- GROUP BY 이용
SELECT A
, B
, C
, MAX(CASE WHEN D ='201901' THEN E END ) JAN_E
, MAX(CASE WHEN D ='201901' THEN F END ) JAN_F
, MAX(CASE WHEN D ='201902' THEN E END ) FEB_E
, MAX(CASE WHEN D ='201902' THEN F END ) FEB_F
, MAX(CASE WHEN D ='201903' THEN E END ) MAR_E
, MAX(CASE WHEN D ='201903' THEN F END ) MAR_F
FROM T
GROUP BY A , B , C
-- PIVOT 이용
SELECT *
FROM T
PIVOT ( MAX(E) E , MAX(F) F FOR D IN ( 201901 , 201902 , 201903 ))
by 우리집아찌
[2019.01.15 11:10:17]
WITH T( A,B,C,D,E,F) AS (
SELECT '사과','서울','Apple', 201901, 150, 1150 FROM DUAL UNION ALL
SELECT '사과','서울','Apple', 201902, 250, 1250 FROM DUAL UNION ALL
SELECT '사과','서울','Apple', 201903, 350, 1350 FROM DUAL UNION ALL
SELECT '딸기','인천','Strawberry', 201901, 450, 1450 FROM DUAL UNION ALL
SELECT '딸기','인천','Strawberry', 201902, 550, 1550 FROM DUAL UNION ALL
SELECT '딸기','인천','Strawberry', 201903, 650, 1650 FROM DUAL UNION ALL
SELECT '포도','목포','grape', 201901, 750, 1750 FROM DUAL UNION ALL
SELECT '포도','목포','grape', 201902, 850, 1850 FROM DUAL UNION ALL
SELECT '포도','목포','grape', 201903, 950, 1950 FROM DUAL UNION ALL
SELECT '감자','강원','potato', 201901, 150, 2050 FROM DUAL UNION ALL
SELECT '감자','강원','potato', 201902, 250, 2150 FROM DUAL UNION ALL
SELECT '감자','강원','potato', 201903, 350, 2250 FROM DUAL
)
-- GROUP BY 이용
SELECT A
, B
, C
, GB
, MAX(CASE WHEN D ='201901' THEN AMT END ) "201901"
, MAX(CASE WHEN D ='201902' THEN AMT END ) "201902"
, MAX(CASE WHEN D ='201903' THEN AMT END ) "201903"
FROM (SELECT T.A
, T.B
, T.C
, T.D
, CASE WHEN B.LV = 1 THEN '금주판매' ELSE '금주매입' END GB
, CASE WHEN B.LV = 1 THEN T.E ELSE T.F END AMT
FROM T T
, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 2 ) B
)
GROUP BY A , B , C , GB
ORDER BY C , GB DESC
-- PIVOT / UNPIVOT 이용
SELECT *
FROM (
SELECT *
FROM T
UNPIVOT ( AMT FOR GB IN (E AS '금주판매' , F AS '금주매입') )
)
PIVOT ( MAX(AMT) FOR D IN ( 201901 , 201902 , 201903 ))
ORDER BY C , GB DESC
by 마농
[2019.01.16 08:14:10]
SELECT * FROM t UNPIVOT ( amt FOR gb IN (e AS '금주판매', f AS '금주매입') ) PIVOT ( MAX(amt) FOR d IN (201901, 201902, 201903)) ORDER BY c, gb DESC ;
by 우리집아찌
[2019.01.16 10:40:59]
굳이 인라이뷰 쓸필요가 없었네요 ^^*
by 홍길덩이
[2019.01.14 17:58:49]
우리집아찌님께서 답변주신 쿼리에
같은내용의 여러건이 있을경우 MAX -> SUM 으로 하셔도 좋을 것 같습니다!
) 버튼을 클릭하여 작성 하시면 됩니다.