如何在TSQL的Pivot或CASE Pivot中添加日期列并取最新数据？

原始数据 #

需求 #

• 按ID分组
• 基于Subject列展开透视
• 取每个ID+Subject组合下日期最新的Value值
• 每个Subject对应独立的Value列和Date列

解决方案1：一次性生成完整透视表 #

核心思路是先用窗口函数筛选出每个ID+Subject的最新记录，再基于这些记录做CASE透视：

WITH LatestRecords AS (
    SELECT 
        [ID],
        [Subject],
        [Value],
        [Date],
        -- 按ID+Subject分组，标记日期最新的行
        ROW_NUMBER() OVER (PARTITION BY [ID], [Subject] ORDER BY [Date] DESC) AS RowNum
    FROM Table1
)
SELECT 
    [ID],
    -- 透视Field的Value和日期
    MAX(CASE WHEN [Subject] = 'Field' THEN [Value] END) AS FieldValue,
    MAX(CASE WHEN [Subject] = 'Field' THEN [Date] END) AS FieldDate,
    -- 透视Amount的Value和日期
    MAX(CASE WHEN [Subject] = 'Amount' THEN [Value] END) AS AmountValue,
    MAX(CASE WHEN [Subject] = 'Amount' THEN [Date] END) AS AmountDate
FROM LatestRecords
WHERE RowNum = 1 -- 仅保留最新行
GROUP BY [ID]

查询结果 #

解决方案2：分Subject单独查询 #

如果不需要一次性生成完整表，可以针对每个Subject单独查询：

针对Field的查询 #

WITH LatestFieldRecords AS (
    SELECT 
        [ID],
        [Value] AS FieldValue,
        [Date] AS FieldDate,
        ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [Date] DESC) AS RowNum
    FROM Table1
    WHERE [Subject] = 'Field'
)
SELECT 
    t.[ID],
    lfr.FieldValue,
    lfr.FieldDate
FROM (SELECT DISTINCT [ID] FROM Table1) t
LEFT JOIN LatestFieldRecords lfr ON t.[ID] = lfr.[ID] AND lfr.RowNum = 1

查询结果

针对Amount的查询 #

WITH LatestAmountRecords AS (
    SELECT 
        [ID],
        [Value] AS AmountValue,
        [Date] AS AmountDate,
        ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [Date] DESC) AS RowNum
    FROM Table1
    WHERE [Subject] = 'Amount'
)
SELECT 
    t.[ID],
    lar.AmountValue,
    lar.AmountDate
FROM (SELECT DISTINCT [ID] FROM Table1) t
LEFT JOIN LatestAmountRecords lar ON t.[ID] = lar.[ID] AND lar.RowNum = 1

查询结果

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