세로값을 가로로 출력

SYS_CONNECT_BY_PATH()함수를 BOM구조에서뿐만 아니라 BOM구조가 아닌경우에도 사용할 수 있다.

 

세로로 되어있는 형태

 

원하는 형태 : 가로로 출력

   

성분코드를 그룹코드별로 화면에 가로로 출력하고 싶을때 사용하는 SQL

-- 테이블 생성 스크립트

DROP TABLE ZZ_TAB1;
CREATE TABLE ZZ_TAB1 (
 ITEM_SEQ NUMBER ,
 TOTAL_SEQ NUMBER,
 INGR_SEQ NUMBER,
 INGR_CODE VARCHAR2(6),
 INGR_QTY VARCHAR2(10),
 COSMETIC_MIX_GROUP_SEQ NUMBER,
 SORT_KEY NUMBER
)
;

DROP TABLE DRC_INGR
CREATE TABLE DRC_INGR (
 INGR_CODE VARCHAR2(6),
 INGR_KORNAME VARCHAR2(50)
);

 

-- 테스트 데이터 생성
INSERT INTO ZZ_TAB1 VALUES (2006262803,1,1,'008186','0.291',1,2);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,2,'008187','0.291',1,1);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,3,'008188','0.291',1,3);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,4,'008181','0.291',1,4);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,5,'008182','0.301',2,1);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,6,'008183','0.301',2,2);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,7,'008184','0.301',2,4);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,8,'008185','0.301',2,5);
INSERT INTO ZZ_TAB1 VALUES(2006262803,1,9,'008189','0.301',2,3);

 

INSERT INTO DRC_INGR VALUES ('008187','동충하초');
INSERT INTO DRC_INGR VALUES ('008186','동청');
INSERT INTO DRC_INGR VALUES ('008188','두시');
INSERT INTO DRC_INGR VALUES ('008181','대청엽');
INSERT INTO DRC_INGR VALUES ('008182','대풍자');
INSERT INTO DRC_INGR VALUES ('008183','동과자');
INSERT INTO DRC_INGR VALUES ('008189','두충엽');
INSERT INTO DRC_INGR VALUES ('008184','동과피');
INSERT INTO DRC_INGR VALUES ('008185','동규자');
COMMIT;

SELECT       ITEM_SEQ
                 ,TOTAL_SEQ
                 ,COSMETIC_MIX_GROUP_SEQ
                 ,SUBSTR(MAX (SYS_CONNECT_BY_PATH (INGR_SEQ, '/')), 2) INGR_SEQ
                 ,SUBSTR(MAX (SYS_CONNECT_BY_PATH (INGR_CODE, '/')), 2) INGR_CODE
                 ,SUBSTR (MAX (SYS_CONNECT_BY_PATH (T1.INGR_KOR_NAME, '/')), 2)  INGR_KOR_NAME
                ,MAX(INGR_QTY) INGR_QTY
FROM   (
    SELECT  ITEM_SEQ,
                 TOTAL_SEQ,
                 INGR_SEQ,
                 T1.INGR_CODE,
                 T2.INGR_KOR_NAME,
                 INGR_QTY,
                 COSMETIC_MIX_GROUP_SEQ,
                 SORT_KEY RNUM  -- 칼럼에 소트키가 있을때
                 --ROW_NUMBER () OVER (PARTITION BY COSMETIC_MIX_GROUP_SEQ ORDER BY ROWNUM) rnum  -- 칼럼에 별도의 소트키가 없을때

   FROM  ZZ_TAB1 T1, DRC_INGR T2
   WHERE  T1.INGR_CODE = T2.INGR_CODE
         ) T1
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR COSMETIC_MIX_GROUP_SEQ = COSMETIC_MIX_GROUP_SEQ
  GROUP BY   ITEM_SEQ
         ,TOTAL_SEQ
         ,COSMETIC_MIX_GROUP_SEQ
;

[출처] 세로값을 가로로 출력|작성자 리원아빠