基于ID与Timestamp双列表的SQL Pivot透视表实现求助

要实现单个id对应多个唯一timestamp列的透视表，需要先处理重复数据，再结合行号生成与Pivot函数完成转换，以下是具体步骤和代码示例：

1. 先对原始数据去重 #

首先去除每个id下重复的timestamp记录，得到唯一的id-timestamp组合：

SELECT DISTINCT id, timestamp
INTO #UniqueTimestamps
FROM YourTableName;

（替换YourTableName为你的实际表名）

2. 为每个id下的timestamp生成序号 #

用窗口函数给每个id的唯一timestamp分配递增序号，后续Pivot需要用这个序号来生成列：

SELECT 
    id,
    timestamp,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY timestamp) AS TimestampSeq
INTO #NumberedTimestamps
FROM #UniqueTimestamps;

3. 使用Pivot转换为透视表 #

静态Pivot（已知最大列数） #

如果提前知道每个id最多有N个唯一timestamp，可以直接写静态Pivot语句。比如示例数据中id20598000有5个唯一timestamp，所以可以写：

SELECT 
    id,
    [1] AS Timestamp_1,
    [2] AS Timestamp_2,
    [3] AS Timestamp_3,
    [4] AS Timestamp_4,
    [5] AS Timestamp_5
FROM #NumberedTimestamps
PIVOT (
    MAX(timestamp)
    FOR TimestampSeq IN ([1], [2], [3], [4], [5])
) AS PivotTable;

动态Pivot（自动适配列数） #

如果不确定每个id的timestamp数量，用动态SQL自动生成列：

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

-- 生成所有需要的列名
SELECT @cols = STRING_AGG(QUOTENAME(TimestampSeq), ', ')
FROM (SELECT DISTINCT TimestampSeq FROM #NumberedTimestamps) AS Seq;

-- 构建动态Pivot查询
SET @query = '
SELECT id, ' + @cols + '
FROM #NumberedTimestamps
PIVOT (
    MAX(timestamp)
    FOR TimestampSeq IN (' + @cols + ')
) AS PivotTable';

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

清理临时表（可选） #

如果用了临时表，最后可以清理：

DROP TABLE #UniqueTimestamps;
DROP TABLE #NumberedTimestamps;

示例结果 #

以id20598000为例，转换后会得到：

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