오라클 listagg 함수
DB CAFE
thumb_up 추천메뉴 바로가기
- 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, E20SELECT 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 0SELECT 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
;