SQL Server如何将同ID子记录转为同行列而非新行？

问题描述 #

现有数据中每个ID对应多条重复行，需要将同一ID的子记录合并到同一行，并重复原列标题（例如生成FirstName1、LastName1、FirstName2、LastName2这类列）。以下是我的尝试代码，求实现预期结果的方法：

尝试代码 #

if Object_id('tempdb..#temp1') is not null
Begin
 drop table #temp1
End
create table #temp1 (
    ID integer, FirstName varchar(50), LastName varchar(50)
)
insert into #temp1 values (25,'Abby','Mathews');
insert into #temp1 values (25,'Jennifer','Edwards');
insert into #temp1 values (26,'Peter','Williams');
insert into #temp1 values (27,'John','Jacobs');
insert into #temp1 values (27,'Mark','Scott');

Select * From #temp1;

With Qrt_CTE (ID, FirstName, LastName)
AS   
(
  SELECT ID, FirstName, LastName 
  FROM #temp1 AS BaseQry
)
SELECT ID, ColumnName, ColumnValue INTO #temp2
FROM Qrt_CTE
UNPIVOT
(
    ColumnValue FOR ColumnName IN (FirstName, LastName)
) AS UnPivotExample

Select * From #temp2

解决方案 #

可以通过行号标记+条件聚合的方式实现，步骤如下：

1. 给每个ID下的记录分配唯一行号，区分同ID的不同子记录；
2. 通过条件聚合，将不同行号的FirstName和LastName映射到对应的新列中。

固定行数场景实现代码 #

如果已知每个ID最多的子记录数量（比如示例中最多2条），可以用静态SQL实现：

-- 先给每个ID的记录添加行号
WITH NumberedRecords AS (
    SELECT 
        ID,
        FirstName,
        LastName,
        -- 按ID分组，给每组内的记录分配行号（可根据实际需求调整排序字段）
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY FirstName) AS RowNum
    FROM #temp1
)
-- 条件聚合生成目标列
SELECT 
    ID,
    MAX(CASE WHEN RowNum = 1 THEN FirstName END) AS FirstName1,
    MAX(CASE WHEN RowNum = 1 THEN LastName END) AS LastName1,
    MAX(CASE WHEN RowNum = 2 THEN FirstName END) AS FirstName2,
    MAX(CASE WHEN RowNum = 2 THEN LastName END) AS LastName2
    -- 若ID最多有更多子记录，可继续扩展对应的CASE语句
FROM NumberedRecords
GROUP BY ID;

执行后会得到如下结果：

动态行数场景实现代码 #

如果不确定每个ID最多有多少条子记录，可用动态SQL自动生成对应列：

DECLARE @maxRows INT, @sql NVARCHAR(MAX), @colList NVARCHAR(MAX);

-- 获取每个ID的最大子记录数
SELECT @maxRows = MAX(RowNum)
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY FirstName) AS RowNum
    FROM #temp1
) t;

-- 生成列的CASE语句
SET @colList = '';
WHILE @maxRows > 0
BEGIN
    SET @colList = @colList + 
        'MAX(CASE WHEN RowNum = ' + CAST(@maxRows AS VARCHAR) + ' THEN FirstName END) AS FirstName' + CAST(@maxRows AS VARCHAR) + ',
        MAX(CASE WHEN RowNum = ' + CAST(@maxRows AS VARCHAR) + ' THEN LastName END) AS LastName' + CAST(@maxRows AS VARCHAR) + ',';
    SET @maxRows = @maxRows - 1;
END;
-- 移除最后一个逗号
SET @colList = LEFT(@colList, LEN(@colList) - 1);

-- 拼接完整SQL并执行
SET @sql = '
WITH NumberedRecords AS (
    SELECT 
        ID,
        FirstName,
        LastName,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY FirstName) AS RowNum
    FROM #temp1
)
SELECT ID, ' + @colList + '
FROM NumberedRecords
GROUP BY ID;';

EXEC sp_executesql @sql;

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