SQL动态行转列问题：如何将多订单行转为未知数量列

我是SQL新手，需要把多行订单数据动态转换为数量未知的列（行转列），但自己写的动态SQL只返回了order_no字段，缺少order_dt、attend_dt等其他订单详情，求修正方法。

示例数据 #

IF OBJECT_ID('tempdb..#order') IS NOT NULL DROP TABLE #order;

CREATE TABLE #order (row INT, customer_no INT, name VARCHAR(500), salutation VARCHAR(500), email VARCHAR(500), order_no INT, order_dt DATETIME, attend_dt DATETIME, time_slot VARCHAR(8), num_tickets INT)
INSERT INTO #order VALUES
    (1, 246501, 'John Smith', 'Mr. Smith', 'email@email.org', 8675309, '2023-05-04 14:22:32.407', '2023-05-21 10:00:00.000', '11:30 AM', 2)
,   (2, 246501, 'John Smith', 'Mr. Smith', 'email@email.org', 8675310, '2023-05-05 12:22:32.407', '2023-05-21 10:00:00.000', '1:30 PM', 2)
,   (3, 246501, 'John Smith', 'Mr. Smith', 'email@email.org', 8675311, '2023-05-06 15:22:32.407', '2023-05-21 10:00:00.000', '3:30 PM', 2)
;

期望结果 #

当前问题代码 #

DECLARE @cols NVARCHAR(MAX)
,       @query NVARCHAR(MAX)

SET @cols = STUFF((SELECT ',' +  + QUOTENAME(row)
from #order
group by row
order by row
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

SET @cols = STUFF((SELECT ',' +  + QUOTENAME(row) 
                            from #order
                            group by row
                            order by row
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,'')

SET @query = 'SELECT customer_no,' +  @cols + ' into #orders from 
            (
            select customer_no, row, order_no
            from #order
        ) x
        pivot 
        (
            max(order_no)
            for row in (' + @cols + ')
        ) p; SELECT * 
             FROM #order p
             LEFT OUTER JOIN #orders o ON p.customer_no = o.customer_no'

EXECUTE (@query);

DROP TABLE #order

问题分析 #

1. 仅针对order_no字段做了PIVOT处理，完全没涉及order_dt、attend_dt等其他订单字段；
2. 重复赋值@cols，属于冗余操作；
3. 最后将原表与临时表JOIN，导致返回的是原表多行数据+订单号列，不符合“一行展示所有订单”的需求。

解决方案：多字段动态行转列代码 #

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- 生成所有需要转列的字段名（如order_no_1, order_dt_1...）
SET @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(CONCAT(col, '_', row))
    FROM #order
    CROSS APPLY (
        VALUES 
            ('order_no', order_no),
            ('order_dt', CONVERT(VARCHAR(23), order_dt, 121)),
            ('attend_dt', CONVERT(VARCHAR(23), attend_dt, 121)),
            ('time_slot', time_slot),
            ('num_tickets', CAST(num_tickets AS VARCHAR))
    ) AS ca(col, val)
    ORDER BY ',' + QUOTENAME(CONCAT(col, '_', row))
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 构建动态查询语句
SET @query = N'
WITH Unpivoted AS (
    SELECT 
        customer_no, name, salutation, email,
        CONCAT(col, ''_'', row) AS pivot_col,
        val
    FROM #order
    CROSS APPLY (
        VALUES 
            (''order_no'', CAST(order_no AS VARCHAR)),
            (''order_dt'', CONVERT(VARCHAR(23), order_dt, 121)),
            (''attend_dt'', CONVERT(VARCHAR(23), attend_dt, 121)),
            (''time_slot'', time_slot),
            (''num_tickets'', CAST(num_tickets AS VARCHAR))
    ) AS ca(col, val)
)
SELECT 
    customer_no, name, salutation, email, ' + @cols + '
FROM Unpivoted
PIVOT (
    MAX(val)
    FOR pivot_col IN (' + @cols + ')
) AS p;';

-- 执行动态查询
EXEC sp_executesql @query;

DROP TABLE #order;

代码说明 #

1. UNPIVOT阶段：通过CROSS APPLY把每个订单字段拆成「字段名+行号」的组合列和对应值，把多行多字段的数据转换成多行两列的结构；
2. 生成动态列名：收集所有「字段名+行号」的组合作为最终的列名；
3. PIVOT阶段：把拆分后的行数据转成列，最终每个客户一行，包含所有订单的详情字段。

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