我想创建一个动态数据透视表，我可以将其发布为 sql 服务器中的视图. 请参阅下面的更多详细信息

Question

有2个表格表，一个有acctrefno和date_purchased 表2显示了付款日期和付款金额 以下是表 1 的示例数据 acctrefno FirstPayDate 5 2009-11-05 22 2012-04-15 28 2017-08-15 29 2018-09-15 以下是表 2 的示例数据 acctrefno FirstPayDate date_paid payment_amount 5 2009-11-05 2009-11-13 77.86 5 2009-11-05 2009-12-07 77.86 5 2009-11-05 2010-01-05 77.86 5 2009-11-05 2010-02-05 77.86 5 2009-11-05 2010-03-05 77.86 5 2009-11-05 2010-04-05 77.86 5 2009-11-05 2010-05-05 77.86 5 2009-11-05 2010-06-07 77.86 5 2009-11-05 2010-07-06 77.86 5 2009-11-05 2010-08-05 77.86 5 2009-11-05 2010-09-07 77.86 22 2012-04-15 2012-05-31 173.48 22 2012-04-15 2012-06-11 168.48 22 2012-04-15 2012-06-25 173.48 22 2012-04-15 2012-07-02 168.48 22 2012-04-15 2012-08-13 125.00 22 2012-04-15 2012-08-31 48.48 22 2012-04-15 2012-09-17 125.00 22 2012-04-15 2012-10-10 48.48 22 2012-04-15 2012-10-22 125.00 22 2012-04-15 2012-11-05 48.48 22 2012-04-15 2012-11-13 125.00 28 2017-08-15 2017-08-14 136.00 28 2017-08-15 2017-09-11 170.00 28 2017-08-15 2017-10-17 136.00 28 2017-08-15 2017-11-15 136.00 28 2017-08-15 2017-12-13 170.00 28 2017-08-15 2018-04-16 142.78 28 2017-08-15 2018-05-04 135.98 28 2017-08-15 2018-05-21 102.60 28 2017-08-15 2018-11-20 4.00 28 2017-08-15 2018-11-20 132.00 28 2017-08-15 2018-12-19 8.00 28 2017-08-15 2018-12-19 135.98 28 2017-08-15 2018-12-19 26.02 28 2017-08-15 2019-01-17 4.00 28 2017-08-15 2019-01-17 109.96 28 2017-08-15 2019-01-17 22.04 28 2017-08-15 2019-02-14 4.00 29 2018-09-15 2018-09-17 155.48

我希望获得这样的输出 loan_number月-4个月-3个月-2个月-1个月0个月1个月2个月3个月4月5月6月7月8月9月10月11月12月13日 203026 0.00 0.00 0.00 0.00 77.86 77.86 77.86 77.86 77.86 77.86 77.86 77.86 77.86 77.86 77.86 77.86 77.86 77.86 259796 0.00 0.00 0.00 0.00 0.00 173.48 341.96 168.48 173.48 125.00 173.48 173.48 173.48 216.96 168.48 125.00 221.96 125.00 428086 0.00 0.00 0.00 0.00 136.00 170.00 136.00 136.00 170.00 0.00 0.00 0.00 142.78 238.58 0.00 0.00 0.00 0.00 550343 0.00 0.00 0.00 0.00 155.48 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

这里的第 0 个月表示如果付款日期的月份等于第一个付款日期的月份，则第 1 个月是第一个付款日期后的一个月，而 -1 个月是第一个付款日期之前的一个月。

我想创建动态查询的原因是，在过去 6 年中，有超过 10 万个帐户在不同时间启动，并且具有不同的付款日期。此查询已经有一个有效的解决方案，但问题是它对于当月的列不是动态的。您必须手动指定月份列。

Answer 1

假设你不需要动态SQL

唯一的技巧是将字符串转换为日期，然后计算datediff(MONTH,...)

示例 dbFiddle

Select ID
,[Month 1] = IsNull([1],0)
,[Month 2] = IsNull([2],0)
,[Month 3] = IsNull([3],0)
,[Month 4] = IsNull([4],0)
,[Month 5] = IsNull([5],0)
From  (
Select ID
,Item = DateDiff(MONTH,try_convert(date,replace([Date Purchase],' ',' 1, ')),try_convert(date,replace([Payment Date],',',' ')))
,Value =[Payment Amount]
From YourTable
) src
Pivot ( sum(Value) for Item in ([1],[2],[3],[4],[5]) ) pvt

返回

ID      Month 0  Month 1    Month 2  Month 3    Month 4  Month 5
1550    0.00     120.00     120.00   0.00       0.00     0.00
1551    0.00     130.00     135.00   0.00       90.00    0.00
1552    0.00     0.00       102.00   0.00       900.00   0.00