如何用PIVOT将编号列表转换为固定列数的多行结果

针对你的需求，我们可以通过窗口函数+条件聚合的方式实现，比PIVOT更灵活，也容易扩展到多列。以下是具体方案：

一、单列（Name）的实现方案 #

首先，我们需要给每个CreatedAt分组内的行分配组号和组内列序号，再通过条件聚合将多行转成4列：

WITH GroupedData AS (
    SELECT 
        CreatedAt,
        [Name],
        -- 生成组号：每4行一组
        FLOOR(([Index] - 1) / 4) AS GroupId,
        -- 生成组内列序号（1-4）
        (([Index] - 1) % 4) + 1 AS ColSeq
    FROM #tmpTest
)
SELECT 
    CreatedAt,
    MAX(CASE WHEN ColSeq = 1 THEN [Name] END) AS Name1,
    MAX(CASE WHEN ColSeq = 2 THEN [Name] END) AS Name2,
    MAX(CASE WHEN ColSeq = 3 THEN [Name] END) AS Name3,
    MAX(CASE WHEN ColSeq = 4 THEN [Name] END) AS Name4
FROM GroupedData
GROUP BY CreatedAt, GroupId
ORDER BY CreatedAt, GroupId;

运行结果 #

二、扩展到多列的实现 #

如果你的表还有其他需要按同样规则转换的列（比如Age、Email），只需要在条件聚合中添加对应列的处理即可。假设表新增Age列，示例代码如下：

-- 先修改测试表添加Age列（示例）
ALTER TABLE #tmpTest ADD Age INT;
UPDATE #tmpTest SET Age = 25 WHERE [Name] = 'David';
UPDATE #tmpTest SET Age = 28 WHERE [Name] = 'Haley';
UPDATE #tmpTest SET Age = 30 WHERE [Name] = 'John';
UPDATE #tmpTest SET Age = 26 WHERE [Name] = 'Mark';
UPDATE #tmpTest SET Age = 32 WHERE [Name] = 'Matt';
UPDATE #tmpTest SET Age = 27 WHERE [Name] = 'Sarah';

-- 多列转换的查询
WITH GroupedData AS (
    SELECT 
        CreatedAt,
        [Name],
        Age,
        FLOOR(([Index] - 1) / 4) AS GroupId,
        (([Index] - 1) % 4) + 1 AS ColSeq
    FROM #tmpTest
)
SELECT 
    CreatedAt,
    MAX(CASE WHEN ColSeq = 1 THEN [Name] END) AS Name1,
    MAX(CASE WHEN ColSeq = 2 THEN [Name] END) AS Name2,
    MAX(CASE WHEN ColSeq = 3 THEN [Name] END) AS Name3,
    MAX(CASE WHEN ColSeq = 4 THEN [Name] END) AS Name4,
    MAX(CASE WHEN ColSeq = 1 THEN Age END) AS Age1,
    MAX(CASE WHEN ColSeq = 2 THEN Age END) AS Age2,
    MAX(CASE WHEN ColSeq = 3 THEN Age END) AS Age3,
    MAX(CASE WHEN ColSeq = 4 THEN Age END) AS Age4
FROM GroupedData
GROUP BY CreatedAt, GroupId
ORDER BY CreatedAt, GroupId;

方案说明 #

1. 组号生成：FLOOR(([Index]-1)/4) 确保每4行归为一组，Index从1开始的情况下，1-4对应组0，5-8对应组1，以此类推。
2. 列序号生成：(([Index]-1)%4)+1 给每组内的行分配1-4的序号，对应目标列的位置。
3. 条件聚合：通过MAX(CASE...)提取每组内对应序号的列值，没有对应值时自动填充NULL，完美匹配需求。
4. 扩展性：如果需要调整每行的列数（比如改成5个），只需修改两处的数字4即可，多列扩展只需复制对应列的CASE语句。

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