"오라클 listagg 함수"의 두 판 사이의 차이
DB CAFE
1번째 줄: | 1번째 줄: | ||
− | ListAgg | + | == ListAgg == |
* Row를 Col로 변경 | * Row를 Col로 변경 | ||
23번째 줄: | 23번째 줄: | ||
group by table_name; | group by table_name; | ||
</source> | </source> | ||
+ | |||
+ | |||
+ | == 10g 이전 방식 == | ||
+ | |||
+ | <source lang=sql> | ||
+ | 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 | ||
+ | </source> | ||
+ | |||
+ | <source lang=sql> | ||
+ | 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; | ||
+ | </source> | ||
+ | |||
+ | |||
+ | <source lang=sql> | ||
+ | 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 | ||
+ | </source> | ||
+ | |||
+ | <source lang=sql> | ||
+ | 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 | ||
+ | ; | ||
+ | </source> | ||
+ | <source lang=sql> | ||
+ | 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 | ||
+ | ; | ||
+ | </source> | ||
+ | <source lang=sql> | ||
+ | -- 테이블의 컬럼을 로우를 횡으로 출력 | ||
+ | /* | ||
+ | 즉 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 | ||
+ | ; | ||
+ | </source> | ||
+ | |||
[[Category:oracle]] | [[Category:oracle]] |
2020년 10월 8일 (목) 09:18 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 ListAgg[편집]
- Row를 Col로 변경
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 이전 방식[편집]
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
;
-- 테이블의 컬럼을 로우를 횡으로 출력
/*
즉 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
;