SQL中行转列实现：将同一供应商订单数据合并为单行

原表结构与示例数据 #

假设你的表名为order_tracking，结构和示例数据如下：

需求说明 #

需要将同一VENDOR和ORDER组合的多行数据合并为一行，按DELIVERY_DATE顺序生成带编号后缀的列，比如DELIVERY_DATE_1、REMARKS_1、USER_1，DELIVERY_DATE_2等。

你尝试的错误代码 #

你之前的PIVOT用法存在明显问题：透视维度错误、聚合逻辑不符合需求，且字段名与原表不匹配，代码如下：

SELECT VENDOR,
         order_number,
        -- delivery_date,
         pickup_date
         reasonf_of_delay,
         user_name
from table
PIVOT
(count(delivery_date)
 FOR order_number
 )

可行解决方案 #

方案1：静态行转列（已知最大行数） #

如果能确定同一VENDOR+ORDER的最大行数（比如最多5行），可以用静态逻辑实现。以下以Oracle为例（适配原日期格式）：

WITH ordered_data AS (
    SELECT 
        VENDOR,
        "ORDER",
        DELIVERY_DATE,
        REMARKS,
        "USER",
        -- 按交付日期给每个分组内的行编号
        ROW_NUMBER() OVER(PARTITION BY VENDOR, "ORDER" ORDER BY DELIVERY_DATE) AS rn
    FROM order_tracking
)
SELECT 
    VENDOR,
    "ORDER",
    -- 提取各编号对应的字段值
    MAX(CASE WHEN rn = 1 THEN DELIVERY_DATE END) AS DELIVERY_DATE_1,
    MAX(CASE WHEN rn = 1 THEN REMARKS END) AS REMARKS_1,
    MAX(CASE WHEN rn = 1 THEN "USER" END) AS USER_1,
    MAX(CASE WHEN rn = 2 THEN DELIVERY_DATE END) AS DELIVERY_DATE_2,
    MAX(CASE WHEN rn = 2 THEN REMARKS END) AS REMARKS_2,
    MAX(CASE WHEN rn = 2 THEN "USER" END) AS USER_2,
    MAX(CASE WHEN rn = 3 THEN DELIVERY_DATE END) AS DELIVERY_DATE_3,
    MAX(CASE WHEN rn = 3 THEN REMARKS END) AS REMARKS_3,
    MAX(CASE WHEN rn = 3 THEN "USER" END) AS USER_3,
    MAX(CASE WHEN rn = 4 THEN DELIVERY_DATE END) AS DELIVERY_DATE_4,
    MAX(CASE WHEN rn = 4 THEN REMARKS END) AS REMARKS_4,
    MAX(CASE WHEN rn = 4 THEN "USER" END) AS USER_4,
    MAX(CASE WHEN rn = 5 THEN DELIVERY_DATE END) AS DELIVERY_DATE_5,
    MAX(CASE WHEN rn = 5 THEN REMARKS END) AS REMARKS_5,
    MAX(CASE WHEN rn = 5 THEN "USER" END) AS USER_5
FROM ordered_data
GROUP BY VENDOR, "ORDER";

如果使用SQL Server，只需将双引号改为方括号[ORDER]、[USER]即可。

方案2：动态行转列（行数不确定） #

如果同一VENDOR+ORDER的行数不固定，可以用动态SQL自动生成对应列。

Oracle版本

DECLARE
    sql_stmt VARCHAR2(4000);
    max_rn NUMBER;
BEGIN
    -- 获取最大的行编号
    SELECT MAX(rn) INTO max_rn
    FROM (
        SELECT ROW_NUMBER() OVER(PARTITION BY VENDOR, "ORDER" ORDER BY DELIVERY_DATE) AS rn
        FROM order_tracking
    );

    -- 拼接动态SQL语句
    sql_stmt := 'WITH ordered_data AS (
                    SELECT 
                        VENDOR,
                        "ORDER",
                        DELIVERY_DATE,
                        REMARKS,
                        "USER",
                        ROW_NUMBER() OVER(PARTITION BY VENDOR, "ORDER" ORDER BY DELIVERY_DATE) AS rn
                    FROM order_tracking
                )
                SELECT VENDOR, "ORDER"';

    FOR i IN 1..max_rn LOOP
        sql_stmt := sql_stmt || ', MAX(CASE WHEN rn = ' || i || ' THEN DELIVERY_DATE END) AS DELIVERY_DATE_' || i;
        sql_stmt := sql_stmt || ', MAX(CASE WHEN rn = ' || i || ' THEN REMARKS END) AS REMARKS_' || i;
        sql_stmt := sql_stmt || ', MAX(CASE WHEN rn = ' || i || ' THEN "USER" END) AS USER_' || i;
    END LOOP;

    sql_stmt := sql_stmt || ' FROM ordered_data GROUP BY VENDOR, "ORDER"';

    -- 执行动态SQL
    EXECUTE IMMEDIATE sql_stmt;
END;
/

SQL Server版本

DECLARE @max_rn INT, @sql NVARCHAR(MAX)

-- 获取最大行号
SELECT @max_rn = MAX(rn)
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY VENDOR, [ORDER] ORDER BY DELIVERY_DATE) AS rn
    FROM order_tracking
) t

-- 拼接SQL
SET @sql = N'WITH ordered_data AS (
                SELECT 
                    VENDOR,
                    [ORDER],
                    DELIVERY_DATE,
                    REMARKS,
                    [USER],
                    ROW_NUMBER() OVER(PARTITION BY VENDOR, [ORDER] ORDER BY DELIVERY_DATE) AS rn
                FROM order_tracking
            )
            SELECT VENDOR, [ORDER]'

DECLARE @i INT = 1
WHILE @i <= @max_rn
BEGIN
    SET @sql += N', MAX(CASE WHEN rn = ' + CAST(@i AS NVARCHAR) + N' THEN DELIVERY_DATE END) AS DELIVERY_DATE_' + CAST(@i AS NVARCHAR)
    SET @sql += N', MAX(CASE WHEN rn = ' + CAST(@i AS NVARCHAR) + N' THEN REMARKS END) AS REMARKS_' + CAST(@i AS NVARCHAR)
    SET @sql += N', MAX(CASE WHEN rn = ' + CAST(@i AS NVARCHAR) + N' THEN [USER] END) AS USER_' + CAST(@i AS NVARCHAR)
    SET @i += 1
END

SET @sql += N' FROM ordered_data GROUP BY VENDOR, [ORDER]'

-- 执行动态SQL
EXEC sp_executesql @sql

内容的提问来源于stack exchange，提问作者Саят Оразов