[SQL] 여러 개 데이터를 1개의 행(Row)으로 출력하기 (Oracle, Mysql, PostgreSQL)

---

오늘은 여러 개의 데이터를 1개 행(Row)으로 출력하는 방법을 알아보겠습니다.

최근 데이터를 집계 결과를 화면으로 뿌려주는 작업을 진행했는데요. 해당 화면에는 집계 대상 데이터의 시퀀스도 함께 노출해야 하는 요구사항이 있었습니다.

DB 예시

List형태로 SEQ 출력

따라서 요구사항은 위 예시처럼 A라는 컬럼을 기준으로 Group 하고, SEQ를 List 형태로 출력해야 하는 것이죠. 하지만 GROUP BY 절은 집계(합계, 평균, 연산 등) 외에는 공통된 기준이 아니면 값을 출력할 수 없습니다. 다시 말하면, 서로 다른 여러 개의 값을 한 행으로 나타낼 수 없습니다.

하지만 다수의 데이터가 하나의 값이면 어떨까요? 위 예시에서 seq_a와 seq_b가 문자열로 묶인 "seq_a, seq_b" 형태로 나오는 것입니다. 이와 같은 기능을 수행하는 SQL 함수가 있습니다. Oracle, MySQL, PostgreSQL에서 사용할 수 있는 함수를 알아보겠습니다.

---

ORACLE

1. LISTAGG

공식적으로 문서화되어 Oracle에서 가장 선호하는 함수.
Oracle 11gR2 이상 사용 가능, 버전 19C 이상은 DISTINCT를 사용하여 중복 제거 가능.

LISTAGG(`A`, `B`) WITHIN GROUP (ORDER BY `C`)

• A는 결합할 컬럼
• B는 값 사이의 구분자
• C는 결합할 문자열 내에서 각 값의 순서 지정

1) 기본 사용 예시

SELECT
    department_id,
    LISTAGG(employee_name, ', ') 
    	WITHIN GROUP (ORDER BY employee_name) AS employee_names
FROM
    employees
GROUP BY
    department_id;

위 예시에서 부서 ID로 그룹화하고 있습니다. 그리고 각 부서에 있는 직원 이름을 알파벳 순으로 정렬한 후 구분자 ', '를 사이로 결합하고 있습니다. LISTAGG를 사용할 때에는 WITHIN GROUP (ORDER BY ,,, )이 필수입니다. 추가로 ORDER BY는 다른 Column이 올 수도 있습니다.

2) NULL 값 처리

LISTAGG(
	COALESCE(employee_name, '없음'), ', ') 
WITHIN GROUP (ORDER BY employee_name) AS employee_names

LISTAGG 함수는 기본적으로 NULL 값을 무시합니다. 만약 NULL 값을 포함하고 싶다면 COALESCE 함수를 포함하고, 다른 값으로 대체할 수 있습니다.

3) 예외처리

LISTAGG(employee_name, ', ') 
	WITHIN GROUP (ORDER BY employee_name) 
    ON OVERFLOW TRUNCATE '...' AS employee_names

LISTAGG 함수는 문자열이 길어지면 ORA-01489 에러를 발생시킬 수 있습니다. Oracle 12.2 이상에서는 ON OVERFLOW절을 사용하여 해결할 수 있습니다. 위 예시는 문자열이 최대 길이를 초과할 경우 '...'로 대체하는 방법입니다.

2. WM_CONCAT 

Oracle에서 제공하는 비공식적인 문자열 집계.
Oracle 11g 이전 버전에서 자주 사용했지만, Oracle에서는 LISTAGG 함수를 선호

WM_CONCAT(A)

• A는 결합할 컬럼

1) 기본 사용 예시

SELECT
    department_id,
    WM_CONCAT(employee_name) AS employee_names
FROM
    employees
GROUP BY
    department_id;

WM_CONCAT 함수는 LISTAGG와 다르게 ORDER BY로 정렬할 수 없습니다. 또한 DISTINCT를 제공하지 않아서 중복제거를 원한다면, 서브쿼리를 생성해야 합니다.

---

MySQL

GROUP_CONCAT

GROUP_CONCAT([DISTINCT] A [ORDER BY B] [SEPARATOR C])

• DISTINCT는 선택사항이며, 중복된 값을 제거합니다.
• A는 결합할 컬럼입니다.
• B는 특정 컬럼으로 정렬 방식을 지정하고, 선택사항입니다.
• C는 구분자입니다. 선택사항이고, 기본값은 쉼표(,)입니다.

1) 기본 사용 예시

SELECT
    department_id,
    GROUP_CONCAT(employee_name ORDER BY employee_name SEPARATOR ', ') AS employee_names
FROM
    employees
GROUP BY
    department_id;

 

2) 중복 값 제거

SELECT
    department_id,
    GROUP_CONCAT(DISTINCT employee_name ORDER BY employee_name SEPARATOR ', ') AS employee_names
FROM
    employees
GROUP BY
    department_id;

2) 최대 길이 설정

GROUP_CONCAT 함수의 기본적인 최대 길이는 1024자입니다. 이 길이는 아래 명령어로 조정할 수 있습니다.

SET SESSION group_concat_max_len = 2048;

 

---

PostgreSQL

STRING_AGG

STRING_AGG(A, B)

• A는 결합할 컬럼입니다.
• B는 문자 사이에 구분자입니다.

1) 기본 사용 예시

SELECT
    department_id,
    STRING_AGG(employee_name, ', ' ORDER BY employee_name) AS employee_names
FROM
    employees
GROUP BY
    department_id;

STRING_AGG 함수도 MySQL의 GROUP CONCAT과 같이 ORDER BY 혹은 DISTINCT를 선택적으로 적용할 수 있습니다. 또한 기본적으로 NULL 값을 무시합니다.