如何通过自定义列将多行数据转换为单行（Pivot操作）

针对你需要将同一UniqID、ServiceFrom、ServiceTo对应的多个ServiceID转换为单行自定义列（PR1至PR6）的需求，以下是不同数据库的实现方案：

1. SQL Server 方案 #

使用原生PIVOT函数，先通过ROW_NUMBER()为每组内的ServiceID生成序号，再进行转置：

WITH RankedServices AS (
    SELECT 
        UniqID,
        ServiceFrom,
        ServiceTo,
        ServiceID,
        'PR' + CAST(ROW_NUMBER() OVER (PARTITION BY UniqID, ServiceFrom, ServiceTo ORDER BY ServiceID) AS VARCHAR(2)) AS PivotColumn
    FROM YourTableName
)
SELECT 
    UniqID,
    ServiceFrom,
    ServiceTo,
    PR1, PR2, PR3, PR4, PR5, PR6
FROM RankedServices
PIVOT (
    MAX(ServiceID)
    FOR PivotColumn IN (PR1, PR2, PR3, PR4, PR5, PR6)
) AS PivotResult;

2. MySQL 方案 #

MySQL无原生PIVOT，采用条件聚合实现，同样需要先生成组内序号：

SELECT 
    UniqID,
    ServiceFrom,
    ServiceTo,
    MAX(CASE WHEN rn = 1 THEN ServiceID END) AS PR1,
    MAX(CASE WHEN rn = 2 THEN ServiceID END) AS PR2,
    MAX(CASE WHEN rn = 3 THEN ServiceID END) AS PR3,
    MAX(CASE WHEN rn = 4 THEN ServiceID END) AS PR4,
    MAX(CASE WHEN rn = 5 THEN ServiceID END) AS PR5,
    MAX(CASE WHEN rn = 6 THEN ServiceID END) AS PR6
FROM (
    SELECT 
        UniqID,
        ServiceFrom,
        ServiceTo,
        ServiceID,
        ROW_NUMBER() OVER (PARTITION BY UniqID, ServiceFrom, ServiceTo ORDER BY ServiceID) AS rn
    FROM YourTableName
) AS RankedServices
GROUP BY UniqID, ServiceFrom, ServiceTo;

3. PostgreSQL 方案 #

采用条件聚合方式实现，逻辑清晰易维护：

SELECT 
    UniqID,
    ServiceFrom,
    ServiceTo,
    MAX(CASE WHEN rn = 1 THEN ServiceID END) AS PR1,
    MAX(CASE WHEN rn = 2 THEN ServiceID END) AS PR2,
    MAX(CASE WHEN rn = 3 THEN ServiceID END) AS PR3,
    MAX(CASE WHEN rn = 4 THEN ServiceID END) AS PR4,
    MAX(CASE WHEN rn = 5 THEN ServiceID END) AS PR5,
    MAX(CASE WHEN rn = 6 THEN ServiceID END) AS PR6
FROM (
    SELECT 
        UniqID,
        ServiceFrom,
        ServiceTo,
        ServiceID,
        ROW_NUMBER() OVER (PARTITION BY UniqID, ServiceFrom, ServiceTo ORDER BY ServiceID) AS rn
    FROM YourTableName
) AS RankedServices
GROUP BY UniqID, ServiceFrom, ServiceTo;

注意事项 #

• 将代码中的YourTableName替换为实际表名
• 若每组ServiceID数量超过6个，需对应增加PR列的CASE语句
• ORDER BY ServiceID可根据需求调整排序逻辑（如按插入时间排序）

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