Oracle APEX中基于列内容筛选指定列生成新表的PLSQL实现

你的需求核心是筛选出满足两个条件的列：Category行对应值为Type A，且ID行对应值大于3。由于列数多达1000个，手动指定列名不现实，需要结合逆透视（UNPIVOT）和动态SQL来实现。

步骤1：筛选符合条件的列名 #

首先通过逆透视将原表的列转换为行，然后聚合每个列对应的Category和ID值，最后筛选出符合条件的列：

WITH unpivoted_data AS (
    -- 将所有非Row Header的列逆透视为行，保留行标识和列值
    SELECT 
        row_header,
        column_name,
        column_value
    FROM your_table
    UNPIVOT INCLUDE NULLS (
        column_value FOR column_name IN (
            -- 动态获取所有条件列，避免手动枚举
            SELECT '"' || column_name || '"' 
            FROM user_tab_columns 
            WHERE table_name = UPPER('your_table') 
              AND column_name != UPPER('Row Header')
        )
    )
),
column_criteria AS (
    -- 聚合每个列的Category和ID值
    SELECT 
        column_name,
        MAX(CASE WHEN row_header = 'Category' THEN column_value END) AS category_val,
        MAX(CASE WHEN row_header = 'ID' THEN column_value END) AS id_val
    FROM unpivoted_data
    GROUP BY column_name
)
-- 筛选符合要求的列
SELECT column_name 
FROM column_criteria
WHERE category_val = 'Type A'
  AND TO_NUMBER(id_val) > 3;

步骤2：动态生成查询SQL #

利用LISTAGG拼接筛选出的列名，生成最终的查询语句，用于获取结果或创建新表：

DECLARE
    v_target_columns VARCHAR2(32767);
    v_final_sql VARCHAR2(32767);
BEGIN
    -- 拼接符合条件的列名（带双引号处理含空格的列名）
    SELECT LISTAGG('"' || column_name || '"', ', ') WITHIN GROUP (ORDER BY column_name)
    INTO v_target_columns
    FROM (
        WITH unpivoted_data AS (
            SELECT 
                row_header,
                column_name,
                column_value
            FROM your_table
            UNPIVOT INCLUDE NULLS (
                column_value FOR column_name IN (
                    SELECT '"' || column_name || '"' 
                    FROM user_tab_columns 
                    WHERE table_name = UPPER('your_table') 
                      AND column_name != UPPER('Row Header')
                )
            )
        ),
        column_criteria AS (
            SELECT 
                column_name,
                MAX(CASE WHEN row_header = 'Category' THEN column_value END) AS category_val,
                MAX(CASE WHEN row_header = 'ID' THEN column_value END) AS id_val
            FROM unpivoted_data
            GROUP BY column_name
        )
        SELECT column_name 
        FROM column_criteria
        WHERE category_val = 'Type A'
          AND TO_NUMBER(id_val) > 3
    );

    -- 生成查询SQL（若要创建新表，替换为CREATE TABLE new_table AS (...)）
    v_final_sql := 'SELECT "Row Header", ' || v_target_columns || ' FROM your_table';
    
    -- 输出SQL（在APEX中可直接执行或绑定到动态区域）
    DBMS_OUTPUT.PUT_LINE(v_final_sql);
    -- EXECUTE IMMEDIATE v_final_sql; -- 若需直接执行，打开此注释
END;
/

在Oracle APEX中的应用 #

1. 动态SQL区域：将上述PL/SQL块生成的v_final_sql作为数据源，绑定到APEX的动态SQL区域，即可展示筛选后的结果。
2. 创建新表：如果需要保存结果，将v_final_sql替换为CREATE TABLE filtered_criteria AS SELECT "Row Header", ' || v_target_columns || ' FROM your_table，执行后生成目标表。

关键说明 #

• 逆透视（UNPIVOT）：解决了列数过多无法手动枚举的问题，将列转换为行后便于统一筛选。
• 动态SQL：自动拼接符合条件的列名，避免手动维护1000+列的繁琐工作。
• 大小写处理：Oracle中含空格或大小写敏感的表/列名需用双引号包裹，SQL中通过UPPER()匹配数据字典中的名称。

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