Oracle SQL按类别实现行转列的方法

需求说明 #

需要将OBJECT表中按TYPE分组的NAME行数据转换为列数据，每个类别对应一列，列内按ID顺序展示该类别下的所有名称，缺失位置留空。

数据表定义及测试数据 #

CREATE TABLE OBJECT(
    ID NUMBER,
    TYPE VARCHAR2(10),
    NAME VARCHAR2(10)
);

INSERT INTO OBJECT (ID, TYPE, NAME ) VALUES 
    (1,'FISH','Shark'),
    (2,'FISH','Carp'),
    (3,'FISH','Salmon'),
    (4,'ANIMAL','Cat'),
    (5,'ANIMAL','Dog'),
    (6,'ANIMAL','Sheep'),
    (7,'ANIMAL','Lion'),
    (8,'TRANS','Car'),
    (9,'TRANS','Bike'),
    (10,'FRUIT','Mango'),
    (11,'FRUIT','Apple'),
    (12,'FRUIT','Orange'),
    (13,'FRUIT','Banana'),
    (14,'FRUIT','Grape');

解决方案 #

使用Oracle的窗口函数生成分组内的行号，再通过PIVOT完成行列转换：

SELECT 
    NVL(FISH, '') AS FISH,
    NVL(ANIMAL, '') AS ANIMAL,
    NVL(TRANS, '') AS TRANS,
    NVL(FRUIT, '') AS FRUIT
FROM (
    -- 给每个类别下的记录按ID排序生成行号
    SELECT 
        TYPE, 
        NAME, 
        ROW_NUMBER() OVER (PARTITION BY TYPE ORDER BY ID) rn 
    FROM OBJECT
)
-- 将TYPE值转换为对应列名
PIVOT (
    MAX(NAME) FOR TYPE IN ('FISH' AS FISH, 'ANIMAL' AS ANIMAL, 'TRANS' AS TRANS, 'FRUIT' AS FRUIT)
)
ORDER BY rn;

逻辑说明 #

1. 生成行号：通过ROW_NUMBER() OVER (PARTITION BY TYPE ORDER BY ID)为每个TYPE分组内的记录按ID顺序生成唯一行号，确保不同类别下的同序名称能对应到结果的同一行。
2. 行列转换：PIVOT子句将TYPE的不同取值直接转为列名，MAX(NAME)作为聚合函数（因每个行号+TYPE组合唯一，聚合结果即为对应名称）。
3. 空值处理：用NVL()函数将结果中的NULL转换为空字符串，优化展示效果。

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