"오라클 listagg 함수"의 두 판 사이의 차이

DB CAFE

2023년 3월 5일 (일) 05:33 기준 최신판

추천메뉴 바로가기

DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
튜닝 { 오라클 튜닝 목록 }
모델링 { 데이터 모델링 가이드 }

1 ListAgg[편집]

Row를 Column 으로 변경

Syntax (12c 개선)

LISTAGG(
    COLUMN, ','
    [ on overflow (truncate|error) ]
    [ text ] [ (with|without) count ]
  ) within group (order by cols)

예시)

select table_name,
         listagg(index_name, ',' on overflow truncate
            'click here'
         ) within group (order by index_name) inds
  from   user_indexes
  group  by table_name;

2 10g 이전 방식[편집]

2.1 테이블의 컬럼을 로우를 횡으로 출력[편집]

/*
    1,
    2,
    3
을 
1,2,3 으로 출력함.
*/

SELECT COLS FROM (

SELECT COLUMN_ID
     , COLUMN_NAME  
     --, SUBSTRB(MIN(SYS_CONNECT_BY_PATH(COLUMN_NAME, ' , ')),4) AS XX       
     , SUBSTRB(MIN(SYS_CONNECT_BY_PATH(COLUMN_NAME, ' , ')),4) AS COLS       
FROM   (
          SELECT A.COLUMN_NAME,COLUMN_ID 
            FROM USER_TAB_COLS A
           WHERE A.TABLE_NAME='TWHP61'
           ORDER BY COLUMN_ID           
           
        ) 
        A 
        START WITH COLUMN_ID = 1 
      CONNECT BY PRIOR COLUMN_ID + 1 = COLUMN_ID
        GROUP BY COLUMN_NAME,COLUMN_ID       
        ORDER BY COLUMN_ID DESC
)
WHERE ROWNUM = 1    
;

SELECT   MAX (DECODE (SABUN_CHECK, '짝수', SABUN_CHECK)) d10  
       , MAX (DECODE (SABUN_CHECK, '짝수', name)) e10  
       , MAX (DECODE (SABUN_CHECK, '짝수', sal)) s10  
       , MAX (DECODE (SABUN_CHECK, '홀수', SABUN_CHECK)) d20  
       , MAX (DECODE (SABUN_CHECK, '홀수', name)) e20  
       , MAX (DECODE (SABUN_CHECK, '홀수', sal)) s20  
    FROM (  
          -------------  
          SELECT   SABUN_CHECK  
                 , name  
                 , SUM (sal) sal  
                 , ROW_NUMBER () OVER   
                      (PARTITION BY SABUN_CHECK ORDER BY 1) rnum  
              FROM   
                
              (  
              ----------  
              SELECT   
                DECODE(MOD(SABUN,2),0,'짝수','홀수') AS SABUN_CHECK,  
                NAME, '1' AS SAL  
              FROM TB_SAWON   
              WHERE ROWNUM <= 10  
              ORDER BY SABUN_CHECK  
              ----------  
              )                
          GROUP BY SABUN_CHECK, ROLLUP(name)  
          -------------  
          )  
GROUP BY rnum  
ORDER BY D10, E20

SELECT BUB_CD,SA_NO
             , MAX(DECODE(GIIL_KINDCD,'21',GIIL_DAY)) GIIL_DAY1
             , MAX(DECODE(GIIL_KINDCD,'36',GIIL_DAY)) GIIL_DAY2
             , MAX(DECODE(GIIL_KINDCD,'71',GIIL_DAY)) GIIL_DAY3                
          FROM (
                SELECT A.BUB_CD
                     , A.SA_NO   
                     , CASE  WHEN A.GIIL_KINDCD = '21' THEN GIIL_KINDCD 
                             WHEN A.GIIL_KINDCD = '36' THEN GIIL_KINDCD
                             WHEN A.GIIL_KINDCD = '71' THEN GIIL_KINDCD               
                             WHEN A.GIIL_KINDCD = '36' THEN GIIL_KINDCD                                                                       
                             ELSE GIIL_KINDCD
                        END AS GIIL_KINDCD   
                     , GIIL_DAY                                                              
                  FROM TWHP21 A
                 WHERE BUB_CD = '000210' 
                   AND GIIL_KINDCD IN('21','36','71')
                   AND SA_NO BETWEEN '20132091000007' AND '20132091000007'  
               )
         GROUP BY BUB_CD,SA_NO

생성

CREATE TABLE CLASS
(
NAME VARCHAR(50),
DETAIL VARCHAR(50),
SDATE DATE
);
COMMIT;

 

삽입

INSERT INTO CLASS VALUES ('ASP','ASP공부',TO_DATE('20070110','YYYYMMDD'));
INSERT INTO CLASS VALUES ('ASP','ASP공부',TO_DATE('20070110','YYYYMMDD'));
INSERT INTO CLASS VALUES ('JSP','JSP공부',TO_DATE('20070210','YYYYMMDD'));
INSERT INTO CLASS VALUES ('JSP','JSP공부',TO_DATE('20070210','YYYYMMDD'));
INSERT INTO CLASS VALUES ('PHP','PHP공부',TO_DATE('20070310','YYYYMMDD'));
COMMIT;

SELECT
 NAME,
 DETAIL,
 COUNT(DECODE(TO_CHAR(SDATE,'MM'),'01',1)) AS "1월",
 COUNT(DECODE(TO_CHAR(SDATE,'MM'),'02',1)) AS "2월",
 COUNT(DECODE(TO_CHAR(SDATE,'MM'),'03',1)) AS "3월",
 COUNT(DECODE(TO_CHAR(SDATE,'MM'),'04',1)) AS "4월",
 COUNT(DECODE(TO_CHAR(SDATE,'MM'),'05',1)) AS "5월",
 COUNT(DECODE(TO_CHAR(SDATE,'MM'),'06',1)) AS "6월",
 COUNT(DECODE(TO_CHAR(SDATE,'MM'),'07',1)) AS "7월",
 COUNT(DECODE(TO_CHAR(SDATE,'MM'),'08',1)) AS "8월",
 COUNT(DECODE(TO_CHAR(SDATE,'MM'),'09',1)) AS "9월",
 COUNT(DECODE(TO_CHAR(SDATE,'MM'),'10',1)) AS "10월",
 COUNT(DECODE(TO_CHAR(SDATE,'MM'),'11',1)) AS "11월",
 COUNT(DECODE(TO_CHAR(SDATE,'MM'),'12',1)) AS "12월"
FROM CLASS
GROUP BY NAME, DETAIL;

 

결과

교육명 세부교육명 1월 ~~~~~~~~~ 12월

ASP ASP공부 2 0 0 0 0 0 0 0 0 0 0 0
PHP PHP공부 0 0 1 0 0 0 0 0 0 0 0 0
JSP JSP공부 0 2 0 0 0 0 0 0 0 0 0 0

SELECT SEIZR_SE_CODE,
       --'(''%'||SUBSTRB(MAX(SYS_CONNECT_BY_PATH(SEIZR_SE_DTLS, '%'' or ''%')), 10)||'%'')' AS SEIZR_SE_DTLS
       SUBSTRB(MAX(SYS_CONNECT_BY_PATH(SEIZR_SE_DTLS, ' or ')),4) AS SEIZR_SE_DTLS       
FROM   (
        SELECT SEIZR_SE_CODE,
                     SEIZR_SE_DTLS,
                     ROW_NUMBER() OVER(PARTITION BY SEIZR_SE_CODE
                                                             ORDER BY SEIZR_SE_CODE) no
          FROM TMD_DTAEXTREQ_SEIZR_INFO
          where  REQST_DE='20120324'
             and    REQST_DE_SN=2         
        ) 
        A START WITH NO = 1 CONNECT BY PRIOR NO + 1 = NO
        AND    PRIOR SEIZR_SE_CODE = SEIZR_SE_CODE
    GROUP BY SEIZR_SE_CODE 
    order by SEIZR_SE_CODE asc
;

SELECT BUB_CD,SA_NO
       , MAX(DECODE(CREDIT_GBNCD,'01','01')) 
       , MAX(DECODE(CREDIT_GBNCD,'01',CREDIT_ORGAMT))
       , MAX(DECODE(CREDIT_GBNCD,'02','02')) 
       , MAX(DECODE(CREDIT_GBNCD,'02',CREDIT_ORGAMT))       
       , MAX(DECODE(CREDIT_GBNCD,'03','03'))       
       , MAX(DECODE(CREDIT_GBNCD,'03',CREDIT_ORGAMT))       
       , MAX(DECODE(CREDIT_GBNCD,'04','04'))         
       , MAX(DECODE(CREDIT_GBNCD,'04',CREDIT_ORGAMT))            
       , MAX(DECODE(CREDIT_GBNCD,'0' ,'0'))       
    FROM (

         SELECT A.BUB_CD
             , A.SA_NO   
             , A.CREDIT_ORGAMT
             , A.CRDMOK_CHASU
             , CASE  WHEN A.CREDIT_GBNCD = '01' THEN CREDIT_GBNCD 
                     WHEN A.CREDIT_GBNCD = '02' THEN CREDIT_GBNCD
                     WHEN A.CREDIT_GBNCD = '03' THEN CREDIT_GBNCD               
                     WHEN A.CREDIT_GBNCD = '04' THEN CREDIT_GBNCD                                      
                     WHEN A.CREDIT_GBNCD = '0'  THEN CREDIT_GBNCD                                  
                    ELSE CREDIT_GBNCD
                END AS CREDIT_GBNCD                                                            
              , RANK() OVER(PARTITION BY BUB_CD,SA_NO ORDER BY BUB_CD,SA_NO,CRDMOK_CHASU DESC) RK
          FROM TWHP111 A         
--         GROUP BY   BUB_CD,SA_NO,CREDIT_GBNCD,CREDIT_ORGAMT --,CRDMOK_CHASU
--         HAVING  CRDMOK_CHASU >= MAX(CRDMOK_CHASU)
--         CRDMOK_CHASU
         )
         WHERE RK = 1
           AND BUB_CD = '000210'
           AND SA_NO = '20132920000001'  
         GROUP BY BUB_CD,SA_NO
         ;