如何无需硬编码将TABLE_A的列按列名拆分为行？

我有如下表 TABLE_A：

可以通过查询all_tab_columns获取该表的列名：

SELECT column_name
FROM all_tab_columns
WHERE table_name = 'TABLE_A';

查询结果：

我希望编写一个查询语句，将TABLE_A中的列拆分为行，使其与上述查询得到的列名匹配，期望结果如下（View 1）：

请问无需硬编码列值能否实现该需求？

可以通过动态SQL实现无硬编码的列转行，核心思路是从all_tab_columns中动态获取需要转换的列名，拼接成对应的查询语句，以下提供两种可行方法：

方法1：动态UNPIVOT（Oracle 11g及以上支持） #

通过PL/SQL生成并执行动态UNPIVOT语句，自动适配表中列的变化：

DECLARE
    v_sql VARCHAR2(4000);
    v_columns VARCHAR2(1000);
BEGIN
    -- 从数据字典获取需转换的列名（排除emp_id），拼接UNPIVOT需要的格式
    SELECT LISTAGG(column_name, ' AS ''' || column_name || '''') WITHIN GROUP (ORDER BY column_id)
    INTO v_columns
    FROM all_tab_columns
    WHERE table_name = 'TABLE_A'
      AND column_name != 'EMP_ID';

    -- 拼接完整的动态SQL语句
    v_sql := 'SELECT emp_id, View_column_1, View_column_2
              FROM TABLE_A
              UNPIVOT (
                  View_column_1 FOR View_column_2 IN (' || v_columns || ')
              )';

    -- 执行语句并创建视图（也可直接输出SQL手动执行）
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW VIEW_1 AS ' || v_sql;
END;
/

方法2：动态UNION ALL（兼容低版本Oracle） #

如果你的Oracle版本不支持UNPIVOT，可以用动态拼接UNION ALL的方式实现：

DECLARE
    v_sql VARCHAR2(4000);
BEGIN
    -- 动态拼接每个列对应的查询，用UNION ALL合并结果
    SELECT LISTAGG(
               'SELECT emp_id, ' || column_name || ' AS View_column_1, ''' || column_name || ''' AS View_column_2 FROM TABLE_A',
               ' UNION ALL '
           ) WITHIN GROUP (ORDER BY column_id)
    INTO v_sql
    FROM all_tab_columns
    WHERE table_name = 'TABLE_A'
      AND column_name != 'EMP_ID';

    -- 创建目标视图
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW VIEW_1 AS ' || v_sql;
END;
/

关键说明 #

• 两种方法都无需硬编码列名，会自动从all_tab_columns中读取TABLE_A的列（排除emp_id）
• 执行完成后，直接查询VIEW_1就能得到你想要的结果
• 如果表属于特定用户，需要在all_tab_columns的查询条件中加上AND owner = '你的用户名'，避免查询到其他用户的同名表
• 若只是临时查询而非创建视图，可将EXECUTE IMMEDIATE替换为DBMS_OUTPUT.PUT_LINE(v_sql)，输出生成的SQL后手动执行即可

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