【Oracle】LISTAGG関数の使い方完全ガイド｜カンマ区切りで文字列を結合・DISTINCT・4000バイト制限の対処法

LISTAGG(結合する列, 区切り文字)
    WITHIN GROUP (ORDER BY ソート列)

SELECT
    LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS all_names
FROM employees;
-- 結果: 佐藤, 鈴木, 田中, 中村, 山田

SELECT
    department_id,
    LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS member_list
FROM employees
GROUP BY department_id
ORDER BY department_id;

SELECT
    employee_id,
    name,
    department_id,
    LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name)
        OVER (PARTITION BY department_id) AS dept_members
FROM employees
ORDER BY department_id, name;

-- カンマ + スペース（最も一般的）
LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name)
-- → 佐藤, 鈴木, 山田

-- パイプ区切り（CSV代替）
LISTAGG(name, ' | ') WITHIN GROUP (ORDER BY name)
-- → 佐藤 | 鈴木 | 山田

-- 改行区切り（CLOBに出力する場合向け）
LISTAGG(name, CHR(10)) WITHIN GROUP (ORDER BY name)
-- → 佐藤
--    鈴木
--    山田

-- 区切りなし（連結）
LISTAGG(name) WITHIN GROUP (ORDER BY name)
-- → 佐藤鈴木山田

-- HTML向け（リスト項目として結合）
LISTAGG(name, '</li><li>') WITHIN GROUP (ORDER BY name)
-- → 佐藤</li><li>鈴木</li><li>山田

-- 重複ありの場合
SELECT
    department_id,
    LISTAGG(DISTINCT job_title, ', ') WITHIN GROUP (ORDER BY job_title) AS job_list
FROM employees
GROUP BY department_id;
-- 同じ job_title が複数人いても1回だけ表示される
-- 結果例: エンジニア, マネージャー, リーダー

SELECT
    department_id,
    LISTAGG(job_title, ', ') WITHIN GROUP (ORDER BY job_title) AS job_list
FROM (
    SELECT DISTINCT department_id, job_title
    FROM employees
)
GROUP BY department_id;

SELECT
    department_id,
    LISTAGG(name, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)
        WITHIN GROUP (ORDER BY name) AS member_list
FROM employees
GROUP BY department_id;
-- 4000バイトを超える場合、末尾が「...(残り15件)」のように切り詰められる

SELECT
    department_id,
    RTRIM(
        XMLAGG(
            XMLELEMENT(e, name || ', ')
            ORDER BY name
        ).EXTRACT('//text()').GETCLOBVAL(),
        ', '
    ) AS member_list
FROM employees
GROUP BY department_id;
-- 結果は CLOB 型で返されるため、4000バイトの制限がない

-- NULLは自動的にスキップされる
SELECT LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS names
FROM employees
WHERE department_id = 10;
-- name が NULL の行は結合結果に含まれない

-- NULLを「(未設定)」として結合に含めたい場合
SELECT
    LISTAGG(NVL(name, '(未設定)'), ', ') WITHIN GROUP (ORDER BY name) AS names
FROM employees
WHERE department_id = 10;

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    LISTAGG(t.tag_name, ', ') WITHIN GROUP (ORDER BY t.tag_name) AS tags
FROM orders o
LEFT JOIN order_tags ot ON o.order_id = ot.order_id
LEFT JOIN tags t ON ot.tag_id = t.tag_id
GROUP BY o.order_id, o.order_date, o.total_amount
ORDER BY o.order_date DESC;

SELECT
    grantee,
    LISTAGG(privilege, ', ' ON OVERFLOW TRUNCATE WITH COUNT)
        WITHIN GROUP (ORDER BY privilege) AS granted_privileges
FROM dba_sys_privs
WHERE grantee NOT IN ('SYS', 'SYSTEM')
GROUP BY grantee
ORDER BY grantee;
-- 結果例: HR → ALTER SESSION, CREATE TABLE, CREATE VIEW, ...

-- メンバーリストに「山田」を含む部門だけを表示する
SELECT
    department_id,
    LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS member_list
FROM employees
GROUP BY department_id
HAVING LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) LIKE '%山田%'
ORDER BY department_id;