SQL Server：如何将数值列转置为两列并按ID聚合取最值？

原始数据 #

ID  Age Date        Value
-------------------------
1   5   01/01/2023  10
1   5   01/04/2023  15
2   7   01/02/2023  17
3   9   10/02/2022  7
3   9   12/20/2022  9

需求条件 #

• 每行对应唯一ID/Age（同一ID的Age始终相同）
• 同一ID有多条记录时取最新Date
• 将Value列转置为Value_1和Value_2两列
• 若ID无第二个Value，Value_2留空
• Value_1为最大值，Value_2为次大值

期望输出 #

ID  Age Date        Value_1   Value_2
-------------------------------------
1   5   01/04/2023    15        10
2   7   01/02/2023    17    
3   9   12/20/2022     9         7

尝试的代码 #

select *
from 
    (select 
         ID, Age, Date,
         Value,
         row_number() over (partition by ID order by Date desc) row_num
     from 
         table) a
where 
    a.row_num = 1

SELECT 
    ID, Age, Date, 'Value_1', 'Value_2'
FROM
    (SELECT 
         ID, Age, Date, Value
     FROM 
         table) AS SourceTable
PIVOT
    (SUM(Value)
     FOR Date IN ('day1', 'day2')
    ) AS PivotTable;

补充说明 #

@dale-k和@t-n的解决方案均有效，若有超过2个值可尝试@t-n的PIVOT方法。

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