SQL基于复合键动态行转列 实现值与时间字段顺序并排展示

核心思路 #

• 首先用窗口函数ROW_NUMBER()对每个(ID1,ID2)分组内的记录按时间升序排序生成唯一序号
• 动态拼接条件聚合的列表达式，保证每个序号对应Val_n和Time_n两个相邻列
• 执行动态SQL完成聚合转列，不需要提前知道每组的记录条数

完整实现代码 #

-- 测试数据定义
DECLARE @DataSource TABLE
(
     ID1 INT
    ,ID2 INT
    ,Val INT
    ,[Time] Datetime
)

INSERT INTO @DataSource (ID1, ID2, Val, [Time])
VALUES   (1,1,10,'01/01/2021 12:00')
        ,(1,1,20,'01/01/2021 15:00')
        ,(1,2,30,'01/02/2021 17:00')
        ,(1,2,35,'01/02/2021 18:00')
        ,(2,1,40,'02/02/2021 08:00')
        ,(2,2,50,'02/02/2021 10:00')
        ,(2,2,60,'05/01/2021 11:00')

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

-- 生成带分组序号的临时数据集
;WITH RankedData AS (
    SELECT 
        ID1,
        ID2,
        Val,
        [Time],
        ROW_NUMBER() OVER(PARTITION BY ID1, ID2 ORDER BY [Time] ASC) AS rn
    FROM @DataSource
)
-- 动态拼接需要返回的列（SQL Server 2017+ 支持STRING_AGG）
SELECT @cols = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN rn = ', rn, ' THEN Val END) AS Val_', rn, ',',
        'MAX(CASE WHEN rn = ', rn, ' THEN [Time] END) AS Time_', rn
    ), ','
) 
FROM (SELECT DISTINCT rn FROM RankedData) t
ORDER BY rn

-- 拼接最终执行的动态SQL
SET @sql = N'
;WITH RankedData AS (
    SELECT 
        ID1,
        ID2,
        Val,
        [Time],
        ROW_NUMBER() OVER(PARTITION BY ID1, ID2 ORDER BY [Time] ASC) AS rn
    FROM @DataSource
)
SELECT 
    ID1,
    ID2,
    ' + @cols + N'
FROM RankedData
GROUP BY ID1, ID2
ORDER BY ID1, ID2
'

-- 执行动态SQL，传递表变量参数
EXEC sp_executesql 
    @stmt = @sql,
    @params = N'@DataSource TABLE (ID1 INT, ID2 INT, Val INT, [Time] Datetime)',
    @DataSource = @DataSource

旧版本兼容说明 #

如果你使用的是SQL Server 2016及更早版本，无法使用STRING_AGG函数，可以将拼接@cols的代码替换为以下FOR XML PATH写法：

SELECT @cols = STUFF((
    SELECT ',' + CONCAT(
        'MAX(CASE WHEN rn = ', rn, ' THEN Val END) AS Val_', rn, ',',
        'MAX(CASE WHEN rn = ', rn, ' THEN [Time] END) AS Time_', rn
    )
    FROM (SELECT DISTINCT rn FROM RankedData) t
    ORDER BY rn
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

输出结果说明 #

执行后返回的列顺序为ID1、ID2、Val_1、Time_1、Val_2、Time_2，完全符合按时间排序后Val和Time两两相邻的要求，每组最多有多少条记录就会自动生成对应数量的列，不需要手动修改代码适配数据量。

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