Oracle数据库动态转置不定Vendor数量的产品表的查询方案问询

你遇到的是典型的动态行转列场景，因为Vendor值会动态新增，无法用静态PIVOT提前枚举所有值，以下是两种无需手动修改查询逻辑的可行方案：

方案1：PIVOT XML原生动态透视（无需修改任何代码，适合应用层解析场景） #

Oracle 11g及以上版本原生支持PIVOT XML语法，不需要提前指定Vendor枚举值，新增Vendor后查询语句完全不用调整，返回的XML结构会自动包含所有Vendor和对应Product_ID。

SELECT *
FROM (
    SELECT Alias, Product_ID, Vendor
    FROM 你的实际表名 -- 替换为你自己的表名
)
PIVOT XML (
    -- 同一个Alias+Vendor唯一的话，用MAX/MIN都可以取值
    MAX(Product_ID)
    -- ANY表示自动匹配当前表中所有存在的Vendor值
    FOR Vendor IN (ANY)
)

返回结果中PIVOT_XML列的结构示例如下，你可以直接在应用层解析XML得到所有动态列：

return (<PivotSet><item><column name = "VENDOR">V1</column><column name = "MAX(PRODUCT_ID)">abc001</column></item><item><column name = "VENDOR">V2</column><column name = "MAX(PRODUCT_ID)">abc002</column></item>...</PivotSet>
)

方案2：动态SQL生成结果（适合需要直接返回关系型多列的场景） #

如果需要数据库直接返回每个Vendor单独一列的二维表结构，可以用存储过程动态拼接查询语句，执行时自动读取当前所有Vendor值生成透视逻辑：

CREATE OR REPLACE FUNCTION get_vendor_pivot RETURN SYS_REFCURSOR IS
    v_sql VARCHAR2(32767);
    v_vendor_list VARCHAR2(32767);
BEGIN
    -- 自动获取当前所有Vendor值，拼接为PIVOT的枚举项
    SELECT LISTAGG(''''||Vendor||''' AS "'||Vendor||'"', ',') WITHIN GROUP (ORDER BY Vendor)
    INTO v_vendor_list
    FROM (SELECT DISTINCT Vendor FROM 你的实际表名);

    -- 拼接完整的透视查询语句
    v_sql := 'SELECT * FROM (SELECT Alias, Product_ID, Vendor FROM 你的实际表名)
              PIVOT (MAX(Product_ID) FOR Vendor IN ('||v_vendor_list||'))
              ORDER BY Alias';

    -- 返回动态查询的结果游标
    RETURN SYS_REFCURSOR(v_sql);
END;
/

调用方式非常简单，新增Vendor后重新调用即可自动生成包含新Vendor列的结果：

SELECT get_vendor_pivot FROM DUAL;

注意事项 #

• 两种方案都适配你提到的50个Vendor上限，LISTAGG默认长度足够支撑，超量可追加ON OVERFLOW TRUNCATE处理
• 如果同一个Alias下同一个Vendor存在多个Product_ID，可将聚合函数MAX替换为LISTAGG(Product_ID, ',')拼接多个ID值

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