sql - 添加当月累积的行

Question

有没有办法将我的硬编码 SQL 脚本转换成动态的？

我对这段代码的问题是，它仍然添加了 SEPT-DEC，它应该是零，因为我们本月还没有覆盖

  SELECT *,
  [JAN] [JAN TO JAN]  ,  
  [JAN] + [FEB]  [JAN TO FEB] ,
  [JAN] + [FEB] + [MAR] [JAN TO MAR],
  [JAN] + [FEB] + [MAR] + [APR] [JAN TO APR]    ,
  [JAN] + [FEB] + [MAR] + [APR] + [MAY] [JAN TO MAY]    ,
  [JAN] + [FEB] + [MAR] + [APR] + [MAY] + [JUN] [JAN TO JUN]    ,
  [JAN] + [FEB] + [MAR] + [APR] + [MAY] + [JUN] + [JUL] [JAN TO JUL],
  [JAN] + [FEB] + [MAR] + [APR] + [MAY] + [JUN] + [JUL] + [AUG] [JAN TO AUG],
  [JAN] + [FEB] + [MAR] + [APR] + [MAY] + [JUN] + [JUL] + [AUG] + [SEP] [JAN TO SEP],
  [JAN] + [FEB] + [MAR] + [APR] + [MAY] + [JUN] + [JUL] + [AUG] + [SEP] + [OCT]  [JAN TO OCT],
  [JAN] + [FEB] + [MAR] + [APR] + [MAY] + [JUN] + [JUL] + [AUG] + [SEP] + [OCT] + [NOV] [JAN TO NOV],
  [JAN] + [FEB] + [MAR] + [APR] + [MAY] + [JUN] + [JUL] + [AUG] + [SEP] + [OCT] + [NOV] + [DEC] [JAN TO DEC]
  FROM TEST_CHANNEL_YTD

我的 table

预期结果:

从一月开始，然后添加后续涵盖的月份。

Answer 1

您可以 UNPIVOT 数据，使用窗口函数 sum() over() 计算运行总计，然后 PIVOT 结果。

示例

Select *
 From  (
        Select Year
              ,Channels
              ,Col
              ,Amt
         From TEST_CHANNEL_YTD A
         Cross Apply ( 
                      Select Col
                            ,Amt = sum(Amt) over (Order by Seq)
                       From (values ('Jan to Jan',1,Jan)
                                   ,('Jan to Feb',2,Feb)
                                   ,('Jan to Mar',3,Mar)
                                   ,('Jan to Apr',4,Apr)
                                   ,('Jan to May',5,May)
                                   ,('Jan to Jun',6,Jun)
                                   ,('Jan to Jul',7,Jul)
                                   ,('Jan to Aug',8,Aug)
                                   ,('Jan to Sep',9,Sep)
                                   ,('Jan to Oct',10,Oct)
                                   ,('Jan to Nov',11,Nov)
                                   ,('Jan to Dec',12,Dec)
                               ) V (Col,Seq,Amt)
                        ) B
       ) src 
 Pivot ( sum(Amt) for Col in ([Jan to Jan],[Jan to Feb],[Jan to Mar],[Jan to Apr],[Jan to May],[Jan to Jun],[Jan to Jul],[Jan to Aug],[Jan to Sep],[Jan to Oct],[Jan to Nov],[Jan to Dec]) ) pvt

结果