Oracle SQL Developer：跨列拼接非'No name'值（替代LISTAGG）

核心思路是先把多列转为行数据，过滤掉不需要的'No name'，再用分组拼接函数实现需求，避免手动写超长的列判断逻辑。

适用于Oracle的实现 #

WITH unpivoted_data AS (
    SELECT 
        ID,
        day_value
    FROM 
        your_table -- 替换成你的表名
    UNPIVOT (
        day_value FOR day_col IN (DAY1, DAY2, DAY3, ..., DAY366)
    )
    WHERE 
        day_value != 'No name'
)
SELECT 
    ID,
    LISTAGG(day_value, ';') WITHIN GROUP (ORDER BY day_col) AS LIST
FROM 
    unpivoted_data
GROUP BY 
    ID;

• UNPIVOT 把DAY1到DAY366的列转成两行数据：day_col（列名，比如DAY1）和day_value（对应列的值）
• 过滤掉值为'No name'的行后，用LISTAGG按ID分组，把有效值用分号拼接，ORDER BY day_col保证拼接顺序和原列顺序一致

如果不想手动写366个列名，可以用动态SQL自动生成列列表：

DECLARE
    cols_str VARCHAR2(4000);
BEGIN
    SELECT LISTAGG('DAY' || LEVEL, ',') WITHIN GROUP (ORDER BY LEVEL)
    INTO cols_str
    FROM DUAL
    CONNECT BY LEVEL <= 366;

    EXECUTE IMMEDIATE '
        WITH unpivoted_data AS (
            SELECT 
                ID,
                day_value
            FROM 
                your_table
            UNPIVOT (
                day_value FOR day_col IN (' || cols_str || ')
            )
            WHERE 
                day_value != ''No name''
        )
        SELECT 
            ID,
            LISTAGG(day_value, '';'') WITHIN GROUP (ORDER BY day_col) AS LIST
        FROM 
            unpivoted_data
        GROUP BY 
            ID
    ';
END;
/

适用于SQL Server的实现 #

SQL Server用STRING_AGG替代LISTAGG，写法类似：

WITH unpivoted_data AS (
    SELECT 
        ID,
        day_value,
        day_col
    FROM 
        your_table
    UNPIVOT (
        day_value FOR day_col IN ([DAY1], [DAY2], [DAY3], ..., [DAY366])
    ) AS up
    WHERE 
        day_value != 'No name'
)
SELECT 
    ID,
    STRING_AGG(day_value, ';') WITHIN GROUP (ORDER BY day_col) AS LIST
FROM 
    unpivoted_data
GROUP BY 
    ID;

适用于PostgreSQL的实现 #

PostgreSQL可以用UNNEST结合数组来转列成行：

SELECT 
    ID,
    STRING_AGG(day_value, ';' ORDER BY idx) AS LIST
FROM 
    your_table,
    UNNEST(ARRAY[DAY1, DAY2, DAY3, ..., DAY366]) WITH ORDINALITY AS t(day_value, idx)
WHERE 
    day_value != 'No name'
GROUP BY 
    ID;

• ARRAY[DAY1,...,DAY366]把多列打包成数组，UNNEST拆成行，WITH ORDINALITY保留原列的顺序索引，保证拼接顺序正确

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