Oracle18c+Apex21.2动态列转置导出Excel技术咨询

针对你遇到的动态属性行转列、导出格式匹配导入模板的需求，以下是几种可行的落地方案：

方案1：利用Apex交互式报表动态列（最简实现） #

直接借助Apex原生能力，无需编写复杂动态SQL：

1. 编写基础关联查询，得到行式数据：

   SELECT p.SKU, a.VALUE AS ATTR_NAME, av.VALUE AS ATTR_VALUE
   FROM 产品表 p
   JOIN T_PRODUCT_VALUES pv ON p.ID = pv.PRODUCT_ID
   JOIN T_ATTRIBUTES_VALUES av ON pv.ATTRIBUTE_VALUE_ID = av.ID
   JOIN T_ATTRIBUTES a ON av.ATTRIBUTE_ID = a.ID
   

2. 在Apex中创建交互式报表，将上述SQL作为数据源
3. 进入报表属性，找到「动态列」设置：
   • 列名来源：选择ATTR_NAME
   • 列值来源：选择ATTR_VALUE
4. 保存后，报表会自动根据T_ATTRIBUTES中的属性动态生成列，属性增减无需修改代码
5. 使用Apex自带的导出功能，选择Excel格式即可得到匹配导入模板的文件

方案2：基于CLOB的动态SQL（绕过4000字节限制） #

Oracle 12c+支持CLOB类型的动态SQL，可解决360个属性导致的语句过长问题：

DECLARE
  v_pivot_cols CLOB;
  v_sql CLOB;
BEGIN
  -- 动态拼接所有属性作为PIVOT列
  SELECT LISTAGG('MAX(CASE WHEN ATTR_NAME = ''' || VALUE || ''' THEN ATTR_VALUE END) AS "' || VALUE || '"', ', ')
         WITHIN GROUP (ORDER BY ID)
  INTO v_pivot_cols
  FROM T_ATTRIBUTES;

  -- 组装完整SQL（CLOB类型无4000字节限制）
  v_sql := 'SELECT SKU, ' || v_pivot_cols || '
            FROM (
              SELECT p.SKU, a.VALUE AS ATTR_NAME, av.VALUE AS ATTR_VALUE
              FROM 产品表 p
              JOIN T_PRODUCT_VALUES pv ON p.ID = pv.PRODUCT_ID
              JOIN T_ATTRIBUTES_VALUES av ON pv.ATTRIBUTE_VALUE_ID = av.ID
              JOIN T_ATTRIBUTES a ON av.ATTRIBUTE_ID = a.ID
            )
            GROUP BY SKU';

  -- 执行动态SQL，可将结果写入临时表或直接作为Apex报表数据源
  EXECUTE IMMEDIATE v_sql;
END;
/

• 若LISTAGG仍存在长度限制，可改用循环拼接每个属性到CLOB变量中
• 在Apex中可将该逻辑封装为存储过程，作为报表的自定义数据源

方案3：Oracle XMLPivot（动态行转列） #

利用Oracle XMLPivot特性，无需硬编码列名：

SELECT *
FROM XMLPIVOT(
  XMLTYPE(
    '<rows>' || (
      SELECT LISTAGG(
               '<row SKU="' || SKU || '" ATTR_NAME="' || ATTR_NAME || '" ATTR_VALUE="' || ATTR_VALUE || '"/>',
               ''
             ) WITHIN GROUP (ORDER BY SKU, ATTR_NAME)
      FROM (
        SELECT p.SKU, a.VALUE AS ATTR_NAME, av.VALUE AS ATTR_VALUE
        FROM 产品表 p
        JOIN T_PRODUCT_VALUES pv ON p.ID = pv.PRODUCT_ID
        JOIN T_ATTRIBUTES_VALUES av ON pv.ATTRIBUTE_VALUE_ID = av.ID
        JOIN T_ATTRIBUTES a ON av.ATTRIBUTE_ID = a.ID
      )
    ) || '</rows>'
  )
  FOR ATTR_NAME IN (SELECT DISTINCT VALUE FROM T_ATTRIBUTES)
  INCLUDE SKU
)

• XMLPivot会自动识别所有属性名称并生成对应列，属性变化时无需修改SQL
• 可直接将此SQL作为Apex报表数据源，导出Excel

方案4：APEX_EXCEL自定义生成（高度可控） #

若需要自定义Excel格式（如单元格样式、冻结窗格等），可使用Apex内置的APEX_EXCEL包：

DECLARE
  l_workbook apex_excel.t_workbook;
  l_sheet apex_excel.t_sheet;
  v_col_idx NUMBER := 2;
BEGIN
  -- 初始化工作簿
  apex_excel.initialize_workbook(l_workbook);
  l_sheet := apex_excel.add_sheet(l_workbook, '产品属性');
  
  -- 写入表头：SKU + 所有属性名称
  apex_excel.set_cell_value(l_sheet, 1, 1, 'PRODUCT_SKU');
  FOR attr_rec IN (SELECT VALUE FROM T_ATTRIBUTES ORDER BY ID) LOOP
    apex_excel.set_cell_value(l_sheet, 1, v_col_idx, attr_rec.VALUE);
    v_col_idx := v_col_idx + 1;
  END LOOP;

  -- 循环写入每个SKU的属性数据
  FOR sku_rec IN (SELECT SKU, ID FROM 产品表) LOOP
    v_col_idx := 2;
    -- 写入SKU
    apex_excel.set_cell_value(l_sheet, apex_excel.get_last_row(l_sheet)+1, 1, sku_rec.SKU);
    
    -- 构建当前SKU的属性值映射
    DECLARE
      TYPE attr_map IS TABLE OF VARCHAR2(2000) INDEX BY VARCHAR2(100);
      v_attr_map attr_map;
    BEGIN
      FOR attr_val_rec IN (
        SELECT a.VALUE AS ATTR_NAME, av.VALUE AS ATTR_VALUE
        FROM T_PRODUCT_VALUES pv
        JOIN T_ATTRIBUTES_VALUES av ON pv.ATTRIBUTE_VALUE_ID = av.ID
        JOIN T_ATTRIBUTES a ON av.ATTRIBUTE_ID = a.ID
        WHERE pv.PRODUCT_ID = sku_rec.ID
      ) LOOP
        v_attr_map(attr_val_rec.ATTR_NAME) := attr_val_rec.ATTR_VALUE;
      END LOOP;

      -- 按属性顺序写入值（无属性则留空）
      FOR attr_rec IN (SELECT VALUE FROM T_ATTRIBUTES ORDER BY ID) LOOP
        apex_excel.set_cell_value(l_sheet, apex_excel.get_last_row(l_sheet), v_col_idx, v_attr_map(attr_rec.VALUE));
        v_col_idx := v_col_idx + 1;
      END LOOP;
    END;
  END LOOP;

  -- 导出Excel文件
  apex_excel.download(l_workbook, '产品属性导出.xlsx');
END;
/

• 该方案完全自定义，可适配复杂格式需求，属性增减自动同步

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