오라클 SYS_CONNECT_BY_PATH와 같은 기능을 하는 DB2 데이터베이스의 LISTAGG 함수
SYS_CONNECT_BY_PATH
오라클에서 계층적 쿼리( 트리 형태 )를 작성할 때 sys_connect_by_path를 사용하게 된다.
루트 노드에서 시작해서 자신의 행까지 반환한다. 첫 번째 파라미터로 컬럼명, 두 번째 파라미터는 char 타입으로 컬럼 간 구분자를 의미한다.
WITH A AS(
SELECT ... FROM ...
), B AS (
SELECT SYS_CONNECT_BY_PATH('컬럼명' , '구분자') AS TREE
FROM employer
START WITH 시작값
CONNECT BY PRIOR 조건절
) 퀴리 성능 보장을 위해 반드시 START WITH 절의 컬럼과 CONNECT BY 절의 컬럼에 인덱스가 있어야 한다.
예제쿼리
SELECT
LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM
employees
START WITH
last_name = 'Kochhar'
CONNECT BY PRIOR
employee_id = manager_id
쿼리 실행결과
/Kochhar
/Kochhar/Greenberg
/Kochhar/Greenberg/Faviet
/Kochhar/Greenberg/Chen
/Kochhar/Greenberg/Sciarra
/Kochhar/Greenberg/Urman
/Kochhar/Greenberg/Popp
/Kochhar/Whalen
/Kochhar/Mavris
/Kochhar/Baer
/Kochhar/Higgins
/Kochhar/Higgins/GietzDB2 오라클 호환형 환경에서 DB2오리지널로 넘어가는 환경에서 동일한 기능을 하도록 쿼리를 컨버팅 할 때 계층형 쿼리를 작성해서 구현해야 하는 자료가 있다면 쿼리를 WITH문으로 작성 후 STEPSQL로 감싸서 사용하면 된다.
그러나 단순하게 구분자를 두고 특정 컬럼값 정보만 나열해야 하는 경우 불편한 계층형 쿼리를 작성하지 않고 DB2에서 제공하는 내부함수 중 LISTAGG를 사용하면 된다. listagg는 오라클에서도 제공하는 함수이다.
listagg예제 쿼리는 다음과 같다.
SELECT A.USERID
, LISTAGG(A.USERNAME, ',') AS USERS_NM
FROM TB_USER유용하게 쓰일 수 있으니 알아두면 좋다.
근데 mssql도 유사한 내부함수가 있을까?
회사에 다닐 때 누군가 만들어놓은 동일한 기능을 하는 함수가 있어서 아무 생각 없이 그냥 사용했기에 문뜩 떠오른 질문이다.
[reference]
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions164.htm
