오라클 컬럼 합치기 ( 오라클 listagg ) - 삽질중인 개발자

- 오라클 컬럼 합치는 방법 -

 

프로그램을 짜다보면 DB에서 조회시 특정 컬럼만 합쳐서 나오게 하고 싶은 경우가 있다.

여러개의 행으로 된 값을 한개의 행으로 합쳐서 가지고 와야하는 경우. 이런경우 오라클에서는 listagg 를 사용하면 편하다.

​

단 listagg 는 오라클 11g 이상 부터 지원하는 기능이다.

이전 버전에서는 wm_concat 을 사용해야 한다.

 

 

우선 사용된 테이블 입니다.

-- oracle 11g 기준입니다.
-- 부서 테이블(tbl_department),  사원 테이블(tbl_employee_info) 이 있습니다.
-- 부서 테이블의 departmentSeq는 사원 테이블의 departmentSeq  참조 되어 있습니다.

create table tbl_department(
    departmentSeq number primary key, --부서 시퀀스
    darpetmentName varchar2(30) --부서명
);

create table tbl_employee_info(
    seq number primary key,--시퀀스
    employeeName varchar2(30),--이름
    departmentSeq references tbl_department(departmentSeq),--부서명
    salary number,   --연봉
    companyCarSupport varchar2(1) --회사 차 지원 여부
);


insert into tbl_department(departmentSeq, darpetmentName) values (1,'영업');
insert into tbl_department(departmentSeq, darpetmentName) values (2,'회계');


insert into tbl_employee_info(seq, departmentSeq, employeeName , salary, companyCarSupport) values (1,1,'아무개',3000,'y');
insert into tbl_employee_info(seq, departmentSeq, employeeName , salary, companyCarSupport) values (2,1,'홍길동',2600,'y');
insert into tbl_employee_info(seq, departmentSeq, employeeName , salary, companyCarSupport) values (3,2,'김상철',5000,'n');
insert into tbl_employee_info(seq, departmentSeq, employeeName , salary, companyCarSupport) values (4,2,'송사리',4400,'n');
insert into tbl_employee_info(seq, departmentSeq, employeeName , salary, companyCarSupport) values (5,1,'박명환',3700,'y');
insert into tbl_employee_info(seq, departmentSeq, employeeName , salary, companyCarSupport) values (6,2,'김지원',3900,'n');
commit;

 

인서트가 완료되면 다음과 같은 쿼리로 데이터를 볼 수 있습니다.

--현재 데이터 상태
SELECT  
    ei.seq,
    ei.employeename,
    d.darpetmentname,
    ei.salary,
    ei.companyCarSupport
FROM
    tbl_employee_info ei,
    tbl_department d
WHERE ei.departmentseq = d.departmentseq;

seq	department	employeeName	salary	companyCarSupport
1	영업	아무개	3000	y
2	영업	홍길동	2600	y
3	회계	김상철	5000	n
4	회계	송사리	4400	n
5	영업	박명환	3700	y
6	회계	김지원	3900	n

 

위와 같은 테이블에서 각 부서별로 누가 속해있는지 아래와 같이 한번에 가지고 와야한다면

department	memberList	companyCarSupport
영업	아무개,홍길동,박명환	y
회계	김상철,송사리,김지원	n

이때 사용하는 함수가 LISTAGG 이다.

 

--사용 방법
LISTAGG( 합쳐지는 컬럼 [,'구분자']) WITHIN GROUP (ORDER BY 정렬하고 싶은 컬럼 ) [OVER (PARTITION BY  그룹을 나누고 싶은 컬럼)]

-- ORDER BY 절이 필수라서 만약 정렬을 원하지 않는 경우 ORDER BY NULL 을 사용하면 된다.
-- OVER 절은 잘 안사용 하고 GROUP BY로 대체하는 경우가 많은것 같다.

이 구문에서는 GROUP BY 로 부서를 묶어주고 LISTAGG를 사용해야지 부서별로 목록을 가지고 온다.

SELECT  
    d.departmentName,
    LISTAGG(ei.employeename,',') WITHIN GROUP (ORDER BY ei.employeename ) AS memberList,
    MAX(ei.companyCarSupport) AS companyCarSupport   --여기 부분 밑에서 설명
FROM
    tbl_employee_info ei,
    tbl_department d
WHERE ei.departmentseq = d.departmentseq
GROUP BY d.departmentName;    -- GROUP BY 여기가 핵심 포인트 부분 부서별로 묶고 나서 -> LISTAGG

결과

max(ei.companyCarSupport) as companyCarSupport -> 만약 컬럼의 값이 각각 다르면 LISTAGG를 서브쿼리 형태로 사용해야한다. 하지만 위와 같은 경우 즉, 회사 방침이 영업부는 차를 지원하고 회계부는 차를 지원을 안해서 같은 부서이면 같은 컬럼값이 나오면 MAX를 사용해서 가지고 오면 영업부는 Y 회계부는 N이 나오게 된다.(GROUP BY를 했기에 가능한 방법) , 사용해야할 경우가 생긴다 알아두면 좋음.

​

두 함수의 차이점

LISTAGG는 컬럼 데이터의 중복을 제거를 안해준다.

WM_CONCAT 을 사용하거나 정규식을 사용해서 걸러주면 된다.