如何使用Oracle PL/SQL Pivot实现含NULL的职业列转置并显示所有数据？

我明白你在使用PL/SQL的Pivot函数转置OCCUPATIONS表时遇到的困扰了——要把职业列转成指定顺序的Doctor、Professor、Singer、Actor列，每个职业下的姓名按字母排序，空缺位置显示NULL对吧？别担心，咱们一步步来搞定这个需求。

核心思路 #

要实现这种“按职业分组排序后转置”的效果，关键是先给每个职业下的姓名分配一个行号，用这个行号作为转置时的对齐依据，确保不同职业的第N个名字能出现在同一行里。

完整SQL代码 #

SELECT 
    Doctor,
    Professor,
    Singer,
    Actor
FROM (
    -- 子查询：给每个职业的姓名按字母顺序分配行号
    SELECT 
        Name,
        Occupation,
        ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name ASC) AS rn
    FROM OCCUPATIONS
)
-- 执行Pivot转置，指定要转置的职业列和对应的列名
PIVOT (
    MAX(Name)  -- 聚合函数：每个行号+职业组合只有一个姓名，MAX/MIN都可
    FOR Occupation IN (
        'Doctor' AS Doctor,
        'Professor' AS Professor,
        'Singer' AS Singer,
        'Actor' AS Actor
    )
)
ORDER BY rn;  -- 按行号排序，保证姓名按顺序从上到下排列

代码解释 #

1. 子查询部分：

   • 使用ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name ASC)给每个职业分组内的姓名按字母升序分配行号（比如Doctor的第一个姓名行号为1，第二个为2，以此类推）。这个行号是转置时对齐的关键。

2. Pivot部分：

   • MAX(Name)作为聚合函数：因为每个(rn, Occupation)组合只会对应一个姓名，所以用MAX或MIN都能准确取出这个唯一值。
   • FOR Occupation IN (...)指定要转置的职业值，并给每个值设置对应的列名，完全符合你要求的列顺序。

3. ORDER BY rn：

   • 确保结果按行号排序，让所有职业的第一个姓名在第一行，第二个在第二行，以此类推，保证输出的顺序符合预期。

示例效果 #

假设你的OCCUPATIONS表数据如下：

运行上述SQL后，输出结果会是：

完全满足你的需求：姓名按字母排序，空缺位置显示NULL，列顺序正确。

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