[Oracle] 오라클 세로를 가로로 (LISTAGG)

세로로 조회된 결과를 한줄(가로로)로 표현하는 방법입니다.

 

<사용법>

1. LISTAGG(대상컬럼, 구분값) WITHIN GROUP( ORDER BY 정렬기준)

 

2. 순위 함수 : RANK() OVER (PARTITION BY 기준컬럼(미필수값) ORDER BY 기준컬럼(필수값) DESC)

- RANK() : 중복 순위 다음은 해당 개수만큼 건너뛰고 반환. (1등이 두명이면 다음 순위는 3등이다.)
- DENSE_RANK() : 중복 순위 상관없이 순차적으로 반환. (1등이 두명이면 다음 순위는 2등이다.)
- ROW_NUMBER() : 중복과 관계 없이 무조건 순서대로 반환. (1등이 두명이어도 1,2등으로 표현된다.)

 

# OVER analytic_clause
해당 함수가 쿼리 결과 집합에 대해 적용되라는 지시어로써 FROM, WHERE, GROUP BY와
HAVING구 이후에 계산되어 진다.
SELECT 구 또는 ORDER BY 구에 Analytic Function을 사용할 수 있다.

- PARTITION BY
하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고 하나 이상의 컬럼 또는
표현식에 의한 그룹으로 쿼리의 결과를 파티션한다.
이 구가 생략되면 단일 그룹처럼 쿼리 결과 집합이 처리된다.

- ORDER BY
하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고
하나 이상의 컬럼 또는 표현식을 기준으로 파티션 내의 데이터를 정렬한다.
표현식은 컬럼의 별칭 또는 위치를 나타내는 숫자를 사용할 수 없다.

 

아래의 예시 결과값은 오라클 라이브 SQL에서 확인 할 수 있습니다. (오라클 11g / 12c 기준)

 

1. 1개의 컬럼에 세로 ROWDATA 합치기

WITH T AS (
    SELECT 1 AS TEXT FROM DUAL
    UNION ALL
    SELECT 2 AS TEXT FROM DUAL
    UNION ALL
    SELECT 3 AS TEXT FROM DUAL
    UNION ALL
    SELECT 4 AS TEXT FROM DUAL
    UNION ALL
    SELECT 5 AS TEXT FROM DUAL
    UNION ALL
    SELECT 6 AS TEXT FROM DUAL
    UNION ALL
    SELECT 7 AS TEXT FROM DUAL
    UNION ALL
    SELECT 8 AS TEXT FROM DUAL
    UNION ALL
    SELECT 9 AS TEXT FROM DUAL
    UNION ALL
    SELECT 10 AS TEXT FROM DUAL
    UNION ALL
    SELECT 11 AS TEXT FROM DUAL
    UNION ALL
    SELECT 12 AS TEXT FROM DUAL
    UNION ALL
    SELECT 13 AS TEXT FROM DUAL
    UNION ALL
    SELECT 14 AS TEXT FROM DUAL
    UNION ALL
    SELECT 15 AS TEXT FROM DUAL
    UNION ALL
    SELECT 16 AS TEXT FROM DUAL
    UNION ALL
    SELECT 17 AS TEXT FROM DUAL
)
SELECT  
   LISTAGG(TEXT, ', ') WITHIN GROUP (ORDER BY TEXT) AS VALUE5
FROM T
 

 

 

2. 컬럼갯수 고정 ROW 동적 증가 (숫자 DATA / number)

EX) 5개의 컬럼 갯수는 고정이고 row 갯수는 데이터에 따라 동적으로 늘었다 줄었다 하는 상황. (숫자 데이터)

WITH T AS (
    SELECT 1 AS TEXT FROM DUAL
    UNION ALL
    SELECT 2 AS TEXT FROM DUAL
    UNION ALL
    SELECT 3 AS TEXT FROM DUAL
    UNION ALL
    SELECT 4 AS TEXT FROM DUAL
    UNION ALL
    SELECT 5 AS TEXT FROM DUAL
    UNION ALL
    SELECT 6 AS TEXT FROM DUAL
    UNION ALL
    SELECT 7 AS TEXT FROM DUAL
    UNION ALL
    SELECT 8 AS TEXT FROM DUAL
    UNION ALL
    SELECT 9 AS TEXT FROM DUAL
    UNION ALL
    SELECT 10 AS TEXT FROM DUAL
    UNION ALL
    SELECT 11 AS TEXT FROM DUAL
    UNION ALL
    SELECT 12 AS TEXT FROM DUAL
    UNION ALL
    SELECT 13 AS TEXT FROM DUAL
    UNION ALL
    SELECT 14 AS TEXT FROM DUAL
    UNION ALL
    SELECT 15 AS TEXT FROM DUAL
    UNION ALL
    SELECT 16 AS TEXT FROM DUAL
    UNION ALL
    SELECT 17 AS TEXT FROM DUAL
)
SELECT  
   SUM(CASE WHEN A.GROUP_NUM = 1 THEN A.TEXT END) AS VALUE1
   ,SUM(CASE WHEN A.GROUP_NUM = 2 THEN A.TEXT END) AS VALUE2
   ,SUM(CASE WHEN A.GROUP_NUM = 3 THEN A.TEXT END) AS VALUE3
   ,SUM(CASE WHEN A.GROUP_NUM = 4 THEN A.TEXT END) AS VALUE4
   ,SUM(CASE WHEN A.GROUP_NUM = 5 THEN A.TEXT END) AS VALUE5
FROM (
    SELECT CEIL(ROWNUM/5) AS TOTAL_ROW
       , TEXT
       , ROW_NUMBER() OVER(PARTITION BY CEIL(ROWNUM/5) ORDER BY TEXT) GROUP_NUM
    FROM T
) A
GROUP BY A.TOTAL_ROW
ORDER BY A.TOTAL_ROW

 

 

3. 컬럼갯수 고정 ROW 동적 증가 (문자열 DATA / vachar2)

EX) 5개의 컬럼 갯수는 고정이고 row 갯수는 데이터에 따라 동적으로 늘었다 줄었다 하는 상황. (문자열 데이터)

WITH T AS (
    SELECT 'A' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'B' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'C' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'D' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'E' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'F' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'G' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'H' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'I' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'J' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'K' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'L' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'M' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'N' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'O' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'T' AS TEXT FROM DUAL
    UNION ALL
    SELECT 'P' AS TEXT FROM DUAL
)
SELECT LISTAGG(A.VALUE1, '') WITHIN GROUP (ORDER BY A.GROUP_NUM) AS VALUE1
      ,LISTAGG(A.VALUE2, '') WITHIN GROUP (ORDER BY A.GROUP_NUM) AS VALUE2
      ,LISTAGG(A.VALUE3, '') WITHIN GROUP (ORDER BY A.GROUP_NUM) AS VALUE3
      ,LISTAGG(A.VALUE4, '') WITHIN GROUP (ORDER BY A.GROUP_NUM) AS VALUE4
      ,LISTAGG(A.VALUE5, '') WITHIN GROUP (ORDER BY A.GROUP_NUM) AS VALUE5
FROM (
    SELECT
       CASE WHEN IA.GROUP_NUM = 1 THEN IA.TEXT END AS VALUE1
       ,CASE WHEN IA.GROUP_NUM = 2 THEN IA.TEXT END AS VALUE2
       ,CASE WHEN IA.GROUP_NUM = 3 THEN IA.TEXT END AS VALUE3
       ,CASE WHEN IA.GROUP_NUM = 4 THEN IA.TEXT END AS VALUE4
       ,CASE WHEN IA.GROUP_NUM = 5 THEN IA.TEXT END AS VALUE5
       , IA.TOTAL_ROW
       , IA.GROUP_NUM
    FROM (
        SELECT CEIL(ROWNUM/5) AS TOTAL_ROW
           , TEXT
           , ROW_NUMBER() OVER(PARTITION BY CEIL(ROWNUM/5) ORDER BY TEXT) GROUP_NUM
        FROM T
    ) IA
    ORDER BY IA.TOTAL_ROW, IA.GROUP_NUM
) A
GROUP BY A.TOTAL_ROW