如何将SQL表数据外推至已知的交易到期日对应列

实现方案 #

针对你需要的到期值向前填充需求，提供两种适配不同场景的实现方案：

固定列静态实现 #

如果日期列固定可直接使用以下查询得到目标结果：

-- 静态查询适配示例中的固定日期列
SELECT 
    ClientName,
    TradeID,
    IIF(CONVERT(DATE,'01/11/2021',103) <= expire_date, expire_val, NULL) AS [01/11/2021],
    IIF(CONVERT(DATE,'01/12/2021',103) <= expire_date, expire_val, NULL) AS [01/12/2021],
    IIF(CONVERT(DATE,'01/01/2022',103) <= expire_date, expire_val, NULL) AS [01/01/2022],
    IIF(CONVERT(DATE,'01/02/2022',103) <= expire_date, expire_val, NULL) AS [01/02/2022],
    IIF(CONVERT(DATE,'01/03/2022',103) <= expire_date, expire_val, NULL) AS [01/03/2022]
FROM (
    SELECT 
        ClientName,
        TradeID,
        -- 取当前行的到期日
        CASE 
            WHEN [01/11/2021] IS NOT NULL THEN CONVERT(DATE,'01/11/2021',103)
            WHEN [01/12/2021] IS NOT NULL THEN CONVERT(DATE,'01/12/2021',103)
            WHEN [01/01/2022] IS NOT NULL THEN CONVERT(DATE,'01/01/2022',103)
            WHEN [01/02/2022] IS NOT NULL THEN CONVERT(DATE,'01/02/2022',103)
            WHEN [01/03/2022] IS NOT NULL THEN CONVERT(DATE,'01/03/2022',103)
        END AS expire_date,
        -- 取当前行唯一的非空到期值
        COALESCE([01/11/2021],[01/12/2021],[01/01/2022],[01/02/2022],[01/03/2022]) AS expire_val
    FROM #t1
) AS t

执行后得到的结果和你给出的#t2完全一致。

动态列通用实现 #

由于你的表是动态生成的，日期列不固定，推荐使用动态SQL自动适配所有列，无需手动调整代码：

DECLARE @col_list NVARCHAR(MAX), @expire_date_case NVARCHAR(MAX), @expire_val_coalesce NVARCHAR(MAX), @sql NVARCHAR(MAX)

-- 读取所有非主键的日期列
SELECT 
    @col_list = STRING_AGG('IIF(CONVERT(DATE,''' + name + ''',103) <= expire_date, expire_val, NULL) AS [' + name + ']', ','),
    @expire_date_case = STRING_AGG('WHEN [' + name + '] IS NOT NULL THEN CONVERT(DATE,''' + name + ''',103)', ' '),
    @expire_val_coalesce = STRING_AGG('[' + name + ']', ',')
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..#t1')
AND name NOT IN ('ClientName', 'TradeID')
ORDER BY CONVERT(DATE, name, 103)

-- 拼接执行SQL
SET @sql = N'
SELECT ClientName, TradeID, ' + @col_list + '
FROM (
    SELECT 
        ClientName,
        TradeID,
        CASE ' + @expire_date_case + ' END AS expire_date,
        COALESCE(' + @expire_val_coalesce + ') AS expire_val
    FROM #t1
) AS t
'

EXEC sp_executesql @sql

该方案会自动识别表中的所有动态生成的日期列，支持任意新增/删除日期列的场景。

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