오라클 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/Gietz

DB2 오라클 호환형 환경에서 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