Oracle SQL中动态生成列：实现研究者姓名与多头衔的同行展示

Got it, let's work through this. You want to display each investigator's name alongside their titles as dynamic columns (title1, title2, title3, etc.)—one row per researcher, with as many title columns as they have titles. Here's how to pull this off in Oracle:

Step 1: Assign Sequential Numbers to Titles #

First, we need to label each title for an investigator with a unique sequence number (1, 2, 3...). This gives us the structure to pivot the rows into columns later. Use the ROW_NUMBER() window function:

SELECT
    name,
    title_name,
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY title_name) AS title_seq
FROM INVESTIGATOR;

• PARTITION BY name: Groups titles by each investigator so the sequence resets for every new researcher.
• ORDER BY title_name: Sorts the titles alphabetically—you can swap this with another column (like a title priority column) if you need a specific order.

Step 2: Dynamic Pivot for Variable Columns #

Since the number of titles per investigator varies, we can't use a static PIVOT (static requires knowing column counts upfront). Instead, we'll build a dynamic SQL query that automatically creates the right number of titleX columns.

Here's a PL/SQL block that does this:

DECLARE
    v_pivot_cols VARCHAR2(4000);
    v_full_sql VARCHAR2(4000);
BEGIN
    -- Generate the list of pivot columns (e.g., '1' AS title1, '2' AS title2, ...)
    SELECT LISTAGG('''' || title_seq || ''' AS title' || title_seq, ', ') WITHIN GROUP (ORDER BY title_seq)
    INTO v_pivot_cols
    FROM (
        SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY name ORDER BY title_name) AS title_seq
        FROM INVESTIGATOR
    );

    -- Build the full pivot query
    v_full_sql := '
        SELECT *
        FROM (
            SELECT
                name,
                title_name,
                ROW_NUMBER() OVER (PARTITION BY name ORDER BY title_name) AS title_seq
            FROM INVESTIGATOR
        )
        PIVOT (
            MAX(title_name)  -- Use MAX since we''re grouping by sequence (each seq has one title)
            FOR title_seq IN (' || v_pivot_cols || ')
        )
        ORDER BY name';

    -- Execute the dynamic query
    EXECUTE IMMEDIATE v_full_sql;
END;
/

How this works: #

1. The LISTAGG function collects all unique title sequence numbers and formats them into the column names needed for the pivot.
2. We construct the full SQL statement with these dynamic columns, then run it using EXECUTE IMMEDIATE.
3. MAX(title_name) is used in the pivot because each sequence number maps to exactly one title per investigator—any aggregate function (like MIN or MAX) works here since there's only one value to aggregate.

Notes to Adjust for Your Use Case #

• If multiple investigators share the same name, use a unique identifier (like investigator_id) in the PARTITION BY clause instead of name to avoid mixing up titles between different people.
• Adjust the ORDER BY in the window function to sort titles according to your needs (e.g., by title creation date, or a custom priority score).

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