如何使用LISTAGG函数拼接多行数据？附APEX 5.0报表场景

Hey there! Let's break down how to implement Oracle's LISTAGG() function in your existing query to merge multiple rows into clean, delimited strings. I'll use your provided inner_table CTE as a base for practical, actionable examples.

Basic LISTAGG() Syntax Recap #

First, a quick refresher on how this function works—it aggregates values from multiple rows into a single string, using a separator you define:

LISTAGG(column_to_concatenate, 'your_separator') 
WITHIN GROUP (ORDER BY sort_column) -- Controls the order of concatenated values (avoids messy results)

If you want to keep all original rows but add a column showing the full concatenated list for a group (e.g., all roles for a user), you can add a partition clause:

LISTAGG(...) WITHIN GROUP (...) OVER (PARTITION BY grouping_column)

Example 1: Collapse Rows by User ID (Merge Roles/Courses) #

Let's assume your inner_table returns multiple rows per user (e.g., one row per role or course they're linked to). Use LISTAGG() in your main query to merge these into single strings, grouping by the user's unique ID and other non-repeating fields:

WITH inner_table AS (
    SELECT DISTINCT 
        i.ID,
        i.name,
        i.lastname,
        CASE i.gender WHEN 'm' THEN 'Male' WHEN 'f' THEN 'Female' END gender,
        i.username,
        b.name region,
        i.address,
        i.city city,
        i.EMAIL,
        r.name AS "ROLE",
        ie.address AS "region_location",
        CASE WHEN i.gender='m' THEN 'blue' WHEN i.gender='f' THEN '#F6358A' END i_color,
        b.course AS COURSE,
        si.city UNIVERSITY,
        CASE WHEN i.id IN (SELECT app_user FROM scholarship) THEN 'check' ELSE 'close' END eligibility_status
    -- Don't forget to add your FROM/JOIN/WHERE clauses here!
    -- e.g., FROM users i JOIN regions b ON i.region_id = b.id JOIN roles r ON i.role_id = r.id ...
)
SELECT 
    ID,
    name,
    lastname,
    gender,
    username,
    region,
    address,
    city,
    EMAIL,
    -- Concatenate all roles for the user, sorted alphabetically, separated by commas
    LISTAGG("ROLE", ', ') WITHIN GROUP (ORDER BY "ROLE") AS concatenated_roles,
    region_location,
    i_color,
    -- Concatenate courses with a pipe separator, sorted by course name
    LISTAGG(COURSE, ' | ') WITHIN GROUP (ORDER BY COURSE) AS concatenated_courses,
    UNIVERSITY,
    eligibility_status
FROM inner_table
GROUP BY 
    ID,
    name,
    lastname,
    gender,
    username,
    region,
    address,
    city,
    EMAIL,
    region_location,
    i_color,
    UNIVERSITY,
    eligibility_status;

Key Notes for This Approach: #

• Group By Requirement: Every non-aggregated column in your SELECT must be included in the GROUP BY clause—Oracle enforces this to avoid ambiguous results.
• Distinct in CTE: Your DISTINCT in inner_table removes duplicate rows before aggregation, which prevents repeated values in your concatenated strings.

Example 2: Show Concatenated Values Without Collapsing Rows #

If you want to keep all original rows but add a column showing the full list of, say, roles for that user, use the OVER (PARTITION BY) clause:

WITH inner_table AS (
    -- Same inner query as above
)
SELECT 
    ID,
    name,
    lastname,
    "ROLE", -- Keep individual role per row
    -- Show all roles for this user on every row
    LISTAGG("ROLE", ', ') WITHIN GROUP (ORDER BY "ROLE") OVER (PARTITION BY ID) AS all_user_roles,
    -- Rest of your columns...
    gender,
    username,
    region,
    eligibility_status
FROM inner_table;

Important Caveats #

• String Length Limits: By default, LISTAGG() returns a VARCHAR2 which has a 4000-character limit in Oracle 11g (common with APEX 5.0). If your concatenated string exceeds this, you'll get an error. For Oracle 12c+, you can handle this with:

  LISTAGG("ROLE", ', ') WITHIN GROUP (ORDER BY "ROLE") ON OVERFLOW TRUNCATE '...' AS concatenated_roles
  

• Null Values: LISTAGG() ignores null values, so if some rows have a null ROLE or COURSE, they won't be included in the concatenated string.

内容的提问来源于stack exchange，提问作者lidagon