LISTAGG (같은 항목끼리 한 줄로 표시하기)

게시일: Feb 08, 2018 5:46:49 AM

- LISTAGG(COLUMN, ', ') WITHIN GROUP (ORDER BY COLUMN)

- LISTAGG(COLUMN, ', ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY COLUMN)

> 길이가 4000바이트를 초과하면 에러가 발생하는데 이럴 때 사용

> 결과값 : 조시장, 홍길동...(2000) -> 2000건이 생략됨

- LISTAGG(COLUMN, ', ' ON OVERFLOW TRUNCATE '[데이터더있음]') WITHIN GROUP (ORDER BY COLUMN)

> 길이가 4000바이트를 초과하면 '[데이터더있음]' 표시함

> 결과값 : 조시장, 홍길동...[데이터더있음] (2000) -> '[데이터더있음]' 표시, 2000건이 생략됨

- LISTAGG(COLUMN, ', ' ON OVERFLOW TRUNCATE '[데이터더있음]') WITHOUT COUNT) WITHIN GROUP (ORDER BY COLUMN)

> 길이가 4000바이트를 초과하면 '[데이터더있음]' 표시함. 건수는 표시하지 않음.

> 결과값 : 조시장, 홍길동...[데이터더있음] -> '[데이터더있음]' 표시, 건수 미출력

select a.o_name

, a.p_name

, a.h_pay

from (

select 'Office3' o_name, '조시장' p_name, 10000 h_pay from dual union all

select 'Office1' o_name, '홍길동' p_name, 13000 h_pay from dual union all

select 'Office2' o_name, '김장군' p_name, 12000 h_pay from dual union all

select 'Office1' o_name, '임꺽정' p_name, 16000 h_pay from dual union all

select 'Office2' o_name, '박장군' p_name, 17000 h_pay from dual union all

select 'Office2' o_name, '정장군' p_name, 11000 h_pay from dual union all

select 'Office1' o_name, '장길산' p_name, 18000 h_pay from dual union all

select 'Office2' o_name, '황장군' p_name, 15000 h_pay from dual union all

select 'Office3' o_name, '최군수' p_name, 14000 h_pay from dual

) a

;

-------------------------------

O_NAME P_NAME H_PAY

-------------------------------

Office3 조시장 10000

Office1 홍길동 13000

Office2 김장군 12000

Office1 임꺽정 16000

Office2 박장군 17000

Office2 정장군 11000

Office1 장길산 18000

Office2 황장군 15000

Office3 최군수 14000

-------------------------------

select a.o_name

, listagg(a.p_name, ', ') within group (order by a.p_name) p_name

--, listagg(a.p_name, ', ' on overflow truncate) within group (order by a.p_name) p_name

--, listagg(a.p_name, ', ' on overflow truncate '[데이터더있음]') within group (order by a.p_name) p_name

--, listagg(a.p_name, ', ' on overflow truncate '[데이터더있음]' without count) within group (order by a.p_name) p_name

, count(*) o_p_cnt

, sum(h_pay) h_pay

from (

select 'Office3' o_name, '조시장' p_name, 10000 h_pay from dual union all

select 'Office1' o_name, '홍길동' p_name, 13000 h_pay from dual union all

select 'Office2' o_name, '김장군' p_name, 12000 h_pay from dual union all

select 'Office1' o_name, '임꺽정' p_name, 16000 h_pay from dual union all

select 'Office2' o_name, '박장군' p_name, 17000 h_pay from dual union all

select 'Office2' o_name, '정장군' p_name, 11000 h_pay from dual union all

select 'Office1' o_name, '장길산' p_name, 18000 h_pay from dual union all

select 'Office2' o_name, '황장군' p_name, 15000 h_pay from dual union all

select 'Office3' o_name, '최군수' p_name, 14000 h_pay from dual

) a

group by a.o_name

order by a.o_name

;

-------------------------------------------------------------------

O_NAME P_NAME O_P_CNT H_PAY

-------------------------------------------------------------------

Office1 임꺽정, 장길산, 홍길동 3 47000

Office2 김장군, 박장군, 정장군, 황장군 4 55000

Office3 조시장, 최군수 2 24000

-------------------------------------------------------------------

[기존 방식] * 계층형 쿼리의 확장 참조

select a.o_name

, ltrim(sys_connect_by_path(a.p_name, ', '), ', ') p_name

, a.o_p_cnt

, a.h_pay

, a.r_num

, level

from

(

select a.o_name

, a.p_name

, row_number() over (partition by a.o_name order by a.p_name) r_num

, count(*) over (partition by a.o_name) o_p_cnt

, sum(a.h_pay) over (partition by a.o_name) h_pay

from (

select 'Office3' o_name, '조시장' p_name, 10000 h_pay from dual union all

select 'Office1' o_name, '홍길동' p_name, 13000 h_pay from dual union all

select 'Office2' o_name, '김장군' p_name, 12000 h_pay from dual union all

select 'Office1' o_name, '임꺽정' p_name, 16000 h_pay from dual union all

select 'Office2' o_name, '박장군' p_name, 17000 h_pay from dual union all

select 'Office2' o_name, '정장군' p_name, 11000 h_pay from dual union all

select 'Office1' o_name, '장길산' p_name, 18000 h_pay from dual union all

select 'Office2' o_name, '황장군' p_name, 15000 h_pay from dual union all

select 'Office3' o_name, '최군수' p_name, 14000 h_pay from dual

) a

) a

where level = a.o_p_cnt

start with a.r_num = 1

connect by

prior a.o_name = a.o_name

and prior a.r_num = a.r_num - 1

order by a.o_name

;

----------------------------------------------------------------------------------

O_NAME P_NAME O_P_CNT H_PAY R_NUM LEVEL

----------------------------------------------------------------------------------

Office1 임꺽정, 장길산, 홍길동 3 47000 3 3

Office2 김장군, 박장군, 정장군, 황장군 4 55000 4 4

Office3 조시장, 최군수 2 24000 2 2

----------------------------------------------------------------------------------