꿈꾸는 개발자, DBA 커뮤니티 구루비

SQL에서 행렬을 변경하면서 두개의 컬럼을 조건으로 할수 있는방법???

by lgxj [Oracle 기초] [2021.04.13 15:07:07]

결과값.png (31,527Bytes)

-- 대략적인 데이타

WITH M_DATA
AS
(
   SELECT 'AAAA' AS A
       ,'A제품' AS B
       ,'95' AS SIZE_CD
       ,'338' AS COL_CD
       ,'10' AS PL_QTY
       ,'12' AS IN__QTY
FROM DUAL
   UNION ALL
   SELECT 'AAAA' AS A
       ,'A제품' AS B
       ,'100' AS SIZE_CD
       ,'303' AS COL_CD
       ,'13' AS PL_QTY
       ,'14' AS IN__QTY
FROM DUAL
   UNION ALL
   SELECT 'AAAA' AS A
       ,'A제품' AS B
       ,'105' AS SIZE_CD
       ,'290' AS COL_CD
       ,'15' AS PL_QTY
       ,'16' AS IN__QTY
FROM DUAL
   UNION ALL
   SELECT 'BBB' AS A
       ,'B제품' AS B
       ,'100' AS SIZE_CD
       ,'303' AS COL_CD
       ,'13' AS PL_QTY
       ,'14' AS IN__QTY
FROM DUAL
   UNION ALL
   SELECT 'BBB' AS A
       ,'B제품' AS B
       ,'105' AS SIZE_CD -- 사이즈
       ,'290' AS COL_CD -- 컬러코드
       ,'15' AS PL_QTY -- 계획수량
       ,'16' AS IN__QTY -- 입고수량
FROM DUAL
)
SELECT *
FROM M_DATA

원하는 결과 값은 파일로 첨부하겠습니다.

※ 조금 구현하기 힘들다하면 그냥 조회시 프로시져로 데이타 생성해서 그냥 조회하는걸로 할까 생각중입니다.

by 마농 [2021.04.13 16:42:13]

컬러에 (컬러와 수량구분) 두가지 정보를 표현하고 있는데. 항목을 두개로 나눠야 하지 않나요?
95, 100, 105 가 레코드가 아닌 컬럼 타이틀로 빠져야 하지 않나요?
현재 5줄로 표현되는 걸 4줄로 표현해야 할 것 같은데요?

WITH m_data AS
(
SELECT 'AAAA' cd, 'A제품' nm, 95 size_cd, 'WHITE' color_cd
, 11 pl_qty, 12 in_qty, 13 ss_qty, 14 tt_qty FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 100, 'WHITE'     , 12, 13, 14, 15 FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 105, 'WHITE'     , 16, 17, 18, 19 FROM dual
UNION ALL SELECT 'AAAA', 'A제품',  95, 'STEEL GRAY', 20, 21, 22, 23 FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 100, 'STEEL GRAY', 24, 25, 26, 27 FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 105, 'STEEL GRAY', 28, 29, 30, 31 FROM dual
)
SELECT *
  FROM m_data
 UNPIVOT (qty FOR gb IN ( pl_qty AS '기획'
                        , in_qty AS '입고'
                        , ss_qty AS '기간판매'
                        , tt_qty AS '총판매'
                        ) )
   PIVOT (MIN(qty) FOR size_cd IN (95, 100, 105))
 ORDER BY cd, color_cd
     , INSTR('기획,입고,기간판매,총판매', gb)
;

by lgxj [2021.04.13 16:55:06]

만약에 사이즈가 레코드로 나오게 끔 하는 방법도 있을까요??

그리고 코드별로 도 나오도록 하는 방법도 있을까요?

by 마농 [2021.04.13 16:58:44]

억지로 만들어 낸다면 가능은 합니다.
다만 결과표를 해석하기가 어려워지죠.
컬러라는 컬럼에 구분이 함께 표현되고.
사이즈라는 컬럼에 수량이 함께 표현되고.
약간 끼워 맞추기식 결과표가 되는 건 아닌가 우려가 될 뿐입니다.

WITH m_data AS
(
SELECT 'AAAA' cd, 'A제품' nm, 95 size_cd, 'WHITE' color_cd
, 11 pl_qty, 12 in_qty, 13 ss_qty, 14 tt_qty FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 100, 'WHITE'     , 12, 13, 14, 15 FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 105, 'WHITE'     , 16, 17, 18, 19 FROM dual
UNION ALL SELECT 'AAAA', 'A제품',  95, 'STEEL GRAY', 20, 21, 22, 23 FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 100, 'STEEL GRAY', 24, 25, 26, 27 FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 105, 'STEEL GRAY', 28, 29, 30, 31 FROM dual
)
SELECT cd, nm
     , NVL(gb, color_cd) color
     , NVL2(gb, MIN(DECODE(size_cd,  95, qty)),  95) size_095
     , NVL2(gb, MIN(DECODE(size_cd, 100, qty)), 100) size_100
     , NVL2(gb, MIN(DECODE(size_cd, 105, qty)), 105) size_105
  FROM m_data
 UNPIVOT (qty FOR gb IN ( pl_qty AS '기획'
                        , in_qty AS '입고'
                        , ss_qty AS '기간판매'
                        , tt_qty AS '총판매'
                        ) )
 GROUP BY cd, nm, color_cd, ROLLUP(gb)
 ORDER BY cd, nm, color_cd
     , GROUPING(gb) DESC
     , INSTR('기획,입고,기간판매,총판매', gb)
;

WITH m_data AS
(
SELECT 'AAAA' cd, 'A제품' nm, 95 size_cd, 'WHITE' color_cd
, 11 pl_qty, 12 in_qty, 13 ss_qty, 14 tt_qty FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 100, 'WHITE'     , 12, 13, 14, 15 FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 105, 'WHITE'     , 16, 17, 18, 19 FROM dual
UNION ALL SELECT 'AAAA', 'A제품',  95, 'STEEL GRAY', 20, 21, 22, 23 FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 100, 'STEEL GRAY', 24, 25, 26, 27 FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 105, 'STEEL GRAY', 28, 29, 30, 31 FROM dual
)
SELECT cd, nm
     , NVL(gb, color_cd) color
     , "95", "100", "105"
  FROM (SELECT cd, nm, size_cd, color_cd, pl_qty, in_qty, ss_qty, tt_qty
             , size_cd sz_qty
          FROM m_data
        )
 UNPIVOT (qty FOR gb IN ( sz_qty AS ''
                        , pl_qty AS '기획'
                        , in_qty AS '입고'
                        , ss_qty AS '기간판매'
                        , tt_qty AS '총판매'
                        ) )
   PIVOT (MIN(qty) FOR size_cd IN (95, 100, 105))
 ORDER BY cd, color_cd
     , INSTR('기획,입고,기간판매,총판매', gb) NULLS FIRST
;

by lgxj [2021.04.13 17:08:51]

일단 분석한번해보겠습니다.

by lgxj [2021.04.14 11:39:09]

여기서 사이즈부분이 동적으로 변경될수는 없 겠죠???

그냥 UNION ALL로 처리를 하는 방법말곤 동적으로는 불가능하겠죠??

by 마농 [2021.04.14 13:01:01]

컬럼 개수 까지는 가변으로 안됩니다. 고정 개수 라고 하면 가능 할 수 있습니다.

WITH m_data AS
(
SELECT 'AAAA' cd, 'A제품' nm, 95 size_cd, 'WHITE' color_cd
, 11 pl_qty, 12 in_qty, 13 ss_qty, 14 tt_qty FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 100, 'WHITE'     , 12, 13, 14, 15 FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 105, 'WHITE'     , 16, 17, 18, 19 FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 100, 'STEEL GRAY', 20, 21, 22, 23 FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 105, 'STEEL GRAY', 24, 25, 26, 27 FROM dual
UNION ALL SELECT 'AAAA', 'A제품', 110, 'STEEL GRAY', 28, 29, 30, 31 FROM dual
)
SELECT cd, nm
     , NVL(gb, color_cd) color
     , size_1, size_2, size_3
  FROM (SELECT cd, nm, color_cd
             , ROW_NUMBER() OVER(PARTITION BY cd, color_cd ORDER BY size_cd) rn
             , size_cd sz_qty
             , pl_qty, in_qty, ss_qty, tt_qty
          FROM m_data
        )
 UNPIVOT (qty FOR gb IN ( sz_qty AS ''
                        , pl_qty AS '기획'
                        , in_qty AS '입고'
                        , ss_qty AS '기간판매'
                        , tt_qty AS '총판매'
                        ) )
   PIVOT (MIN(qty) FOR rn IN (1 size_1, 2 size_2, 3 size_3))
 ORDER BY cd, color_cd
     , INSTR('기획,입고,기간판매,총판매', gb) NULLS FIRST
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변