SQL Server：将多行数据转换为单行多列并自定义列名

你的需求是把每个ID下的多条记录，按Event_ID排序后转成Date1/Dep1、Date2/Dep2这样的成对动态列。原代码仅处理了Dep_Name的透视，缺少对Date字段的处理，以下是修正后的完整实现：

步骤说明 #

1. 给每个ID分组内的记录按Event_ID排序生成行号，作为后续生成动态列的基础。
2. 动态生成所有目标列名：Date1, Dep1, Date2, Dep2, ..., DateN, DepN。
3. 构造动态SQL，通过行号匹配，分别取出每个行号对应的Date和Dep_Name值。

完整代码（SQL Server 2017+） #

BEGIN TRY 
    DROP TABLE ##dep 
END TRY 
BEGIN CATCH 
END CATCH;

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

-- 给每条记录分配行号，临时存储
WITH numbered_data AS (
    SELECT 
        ID,
        Date,
        Dep_Name,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Event_ID) AS rn
    FROM #firstpool
)
-- 生成动态列：Date1, Dep1, Date2, Dep2...
SELECT @cols = STRING_AGG(QUOTENAME('Date' + CAST(rn AS VARCHAR(5))) + ', ' + QUOTENAME('Dep' + CAST(rn AS VARCHAR(5))), ', ')
FROM (SELECT DISTINCT rn FROM numbered_data) AS rns;

-- 生成透视用的行号列名
SELECT @rn_cols = STRING_AGG(QUOTENAME(rn), ', ') WITHIN GROUP (ORDER BY rn)
FROM (SELECT DISTINCT rn FROM numbered_data) AS rns;

-- 构造动态查询语句
SET @query = '
WITH numbered_data AS (
    SELECT 
        ID,
        Date,
        Dep_Name,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Event_ID) AS rn
    FROM #firstpool
)
SELECT 
    ID,
    ' + @cols + '
INTO ##dep
FROM numbered_data
PIVOT (MAX(Date) FOR rn IN (' + @rn_cols + ')) AS pvt_date
JOIN numbered_data
PIVOT (MAX(Dep_Name) FOR rn IN (' + @rn_cols + ')) AS pvt_dep
ON pvt_date.ID = pvt_dep.ID;';

EXECUTE sp_executesql @query;

SELECT * FROM ##dep;

代码解释 #

• numbered_data CTE：给每个ID下的记录按Event_ID排序分配行号，确保记录顺序符合需求。
• 动态列生成：使用STRING_AGG生成所有DateN和DepN的列名，语法简洁高效。
• 双透视关联：分别对Date和Dep_Name进行透视，再通过ID关联，得到成对的列数据。

兼容SQL Server 2016及以下版本的代码 #

若你的SQL Server版本不支持STRING_AGG，可替换为STUFF + FOR XML PATH方式生成动态列：

BEGIN TRY 
    DROP TABLE ##dep 
END TRY 
BEGIN CATCH 
END CATCH;

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

-- 生成动态列：Date1, Dep1, Date2, Dep2...
SET @cols = STUFF((
    SELECT DISTINCT ', ' + QUOTENAME('Date' + CAST(rn AS VARCHAR(5))) + ', ' + QUOTENAME('Dep' + CAST(rn AS VARCHAR(5)))
    FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Event_ID) AS rn FROM #firstpool) AS rns
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 生成透视用的行号列名
SET @rn_cols = STUFF((
    SELECT DISTINCT ', ' + QUOTENAME(rn)
    FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Event_ID) AS rn FROM #firstpool) AS rns
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 构造动态查询语句
SET @query = '
WITH numbered_data AS (
    SELECT 
        ID,
        Date,
        Dep_Name,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Event_ID) AS rn
    FROM #firstpool
)
SELECT 
    ID,
    ' + @cols + '
INTO ##dep
FROM numbered_data
PIVOT (MAX(Date) FOR rn IN (' + @rn_cols + ')) AS pvt_date
JOIN numbered_data
PIVOT (MAX(Dep_Name) FOR rn IN (' + @rn_cols + ')) AS pvt_dep
ON pvt_date.ID = pvt_dep.ID;';

EXECUTE sp_executesql @query;

SELECT * FROM ##dep;

执行后即可得到目标宽表格式，列数会根据每个ID的最大记录数自动生成。

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