请求协助实现SQL Server 2017周列数据按月汇总至目标表
2026-8-16
需求：将按周五日期命名的列按月份求和转换为MonthN列
临时表定义与数据
CREATE TABLE [Table] ( [WC] nvarchar(255), [Requestor] nvarchar(255), [MTR#] nvarchar (255), [Date Added] nvarchar(255), [Use Case] nvarchar(255), [Prog Account] nvarchar(255), [SB#] nvarchar(255), [CO#] nvarchar(255), [Job #] nvarchar(255), [Need Date] nvarchar(255), [Totals] nvarchar, [221] nvarchar(255), [7-Oct-22] nvarchar(255), [14-Oct-22] nvarchar(255), [21-Oct-22] nvarchar(255), [28-Oct-22] nvarchar(255), [4-Nov-22] nvarchar(255), [11-Nov-22] nvarchar(255), [18-Nov-22] nvarchar(255), [25-Nov-22] nvarchar(255), [2-Dec-22] nvarchar(255), [9-Dec-22] nvarchar(255), [16-Dec-22] nvarchar(255), [23-Dec-22] nvarchar(255), [30-DEC-22] nvarchar(255), [6-Jan-23] nvarchar(255), [13-Jan-23] nvarchar(255), [20-Jan-23] nvarchar(255), [27-Jan-23] nvarchar(255), [3-Feb-23] nvarchar(255), [10-Feb-23] nvarchar(255), [17-Feb-23] nvarchar(255), [24-Feb-23] nvarchar(255), [3-Mar-23] nvarchar(255), [10-Mar-23] nvarchar(255), [17-Mar-23] nvarchar(255), [24-Mar-23] nvarchar(255), [31-Mar-23] nvarchar(255), [7-Apr-23] nvarchar(255), [14-Apr-23] nvarchar(255), [21-Apr-23] nvarchar(255), [28-Apr-23] nvarchar(255), [5-May-23] nvarchar(255), [12-May-23] nvarchar(255), [19-May-23] nvarchar(255), [26-May-23] nvarchar(255)) INSERT INTO [dbo].[Table] ([WC] ,[Requestor] ,[MTR#] ,[Date Added] ,[Use Case] ,[Prog Account] ,[SB#] ,[CO#] ,[Job #] ,[Need Date] ,[Totals] ,[221] ,[7-Oct-22] ,[14-Oct-22] ,[21-Oct-22] ,[28-Oct-22] ,[4-Nov-22] ,[11-Nov-22] ,[18-Nov-22] ,[25-Nov-22] ,[2-Dec-22] ,[9-Dec-22] ,[16-Dec-22] ,[23-Dec-22] ,[30-Dec-22] ,[6-Jan-23] ,[13-Jan-23] ,[20-Jan-23] ,[27-Jan-23] ,[3-Feb-23] ,[10-Feb-23] ,[17-Feb-23] ,[24-Feb-23] ,[3-Mar-23] ,[10-Mar-23] ,[17-Mar-23] ,[24-Mar-23] ,[31-Mar-23] ,[7-Apr-23] ,[14-Apr-23] ,[21-Apr-23] ,[28-Apr-23] ,[5-May-23] ,[12-May-23] ,[19-May-23] ,[26-May-23] ) VALUES ('TL','XUpgrade','4523XXXX','4/1/2022', 'Road Kit', 'KKK, 1.1, BA.111','','','','', 0,'123456',1,1,1,2,2,3,3,4,4,5,5,6,6,6,1,7,8,8,1,9,4,9,2,0,9,9,0,8,8,8,7,6,6,6), ('TA','XUpgrade-1','14523XXXX','4/11/2022', 'Tool Kit', 'AAA, 1.10, BA.222','90909','SQAre12','meta0909','4/19/2023', 0,'123456',1,1,1,2,2,3,3,4,4,5,5,6,6,6,7,7,4,3,8,9,2,1,9,9,9,9,0,8,8,8,7,6,6,6), ('TK','XUpgrade-22','24523XXXX','4/12/2022', 'Safe Kit', 'QQQ, 1.11, BA.989','909079','Spare12','metaop0909','9/19/2023', 0,'123456',1,1,1,2,2,3,3,4,4,5,5,6,6,6,7,7,8,8,8,2,2,2,2,2,2,2,0,8,8,8,7,6,6,6), ('TG','XUpgrade-22','34523XXXX','4/13/2022', 'No Kit', 'ASD, 1.19, BA.909','909079','parkre12','metaIns0909','8/19/2023', 0,'123456',1,1,1,2,2,3,3,4,4,5,5,6,6,6,7,7,8,8,8,1,2,3,4,9,9,9,0,8,8,8,7,6,6,6), ('AP','XUpgrade-33','44523XXXX','4/4/2022', 'Yes Kit', 'DFG, 1.881, BA.987','909079','kkkre12','metaFB0909','7/19/2023', 0,'123456',1,1,1,2,2,3,3,4,4,5,5,6,6,0,7,7,8,2,8,1,9,2,1,9,1,9,0,8,8,8,7,6,6,6), ('RL','XUpgrade-00','94523XXXX','4/15/2022', 'Car Kit', 'MNB, 1.991, BA.0909','909079','zzAre12','FB0909','4/19/2023', 0,'123456',1,1,1,2,2,0,3,4,4,5,5,6,6,6,7,7,1,8,8,1,1,1,3,0,1,9,0,8,8,8,7,6,6,6) GO
需求说明
- 临时表前12列为固定字段，剩余列以对应月份的周五日期命名（如2022年10月包含4个周五列）
- 需要将每行中同一月份的所有周五列数值求和，生成对应的
MonthN列（N为月份顺序，从1开始），最终输出到目标表 - 实际数据覆盖25个滚动月份，截止到2024年12月，使用SQL Server 2017（64位）版本
目标表预期输出
[WC]|[Requestor]|[MTR#]|[Date Added]|[Use Case]|[Prog Account]|[SB#]|[CO#]|[Job #]|[Need Date]|[Totals]|[221]|Month1|Month2|Month3|Month4|Month5|Month6|Month7|Month8 TL|XUpgrade|4523XXXX|4/1/2022| Road Kit| KKK, 1.1, BA.111|||||0|123456|5|12|26|22|22|29|24|25 TA|XUpgrade-1|14523XXXX|4/11/2022| Tool Kit| AAA, 1.10, BA.222|90909|SQAre12|meta0909|4/19/2023|0|123456|5|12|26|24|22|37|24|25 TK|XUpgrade-22|24523XXXX|4/12/2022| Safe Kit| QQQ, 1.11, BA.989|909079|Spare12|metaop0909|9/19/2023|0|123456|5|12|26|28|20|10|24|25 TG|XUpgrade-22|34523XXXX|4/13/2022| No Kit| ASD, 1.19, BA.909|909079|parkre12|metaIns0909|8/19/2023|0|123456|5|12|26|28|19|34|24|25 AP|XUpgrade-33|44523XXXX|4/4/2022| Yes Kit| DFG, 1.881, BA.987|909079|kkkre12|metaFB0909|7/19/2023|0|123456|5|12|26|22|20|22|24|25 RL|XUpgrade-00|94523XXXX|4/15/2022| Car Kit| MNB, 1.991, BA.0909|909079|zzAre12|FB0909|4/19/2023|0|123456|5|9|26|21|18|14|24|25
示例：第一行2022年10月的4个周五列值为1、1、1、2，求和后为5，对应
Month1列
解决方案（SQL查询语句）
由于日期列是动态的，采用动态SQL实现，自动识别所有日期列并按月份分组求和：
DECLARE @FixedColumns NVARCHAR(MAX) DECLARE @SumColumns NVARCHAR(MAX) DECLARE @SQL NVARCHAR(MAX) -- 提取前12个固定字段 SELECT @FixedColumns = STRING_AGG(QUOTENAME(COLUMN_NAME), ', ') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table' AND ORDINAL_POSITION <= 12 -- 识别所有日期列，按月份分组生成求和语句 WITH DateColumns AS ( SELECT COLUMN_NAME, -- 解析列名为日期，提取年份和月份 DATEFROMPARTS( CASE WHEN RIGHT(COLUMN_NAME,2) >= '00' AND RIGHT(COLUMN_NAME,2) <= '99' THEN 2000 + CAST(RIGHT(COLUMN_NAME,2) AS INT) ELSE NULL END, MONTH(CAST(COLUMN_NAME AS DATE)), 1 ) AS MonthStart FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table' AND ORDINAL_POSITION > 12 -- 验证列名是否为有效日期 AND TRY_CAST(COLUMN_NAME AS DATE) IS NOT NULL ), MonthGroups AS ( SELECT MonthStart, 'SUM(CAST(' + STRING_AGG(QUOTENAME(COLUMN_NAME), ' AS INT) + CAST(') + ' AS INT)) AS ' + QUOTENAME('Month' + CAST(ROW_NUMBER() OVER(ORDER BY MonthStart) AS VARCHAR(2))) AS SumExpr FROM DateColumns GROUP BY MonthStart ) SELECT @SumColumns = STRING_AGG(SumExpr, ', ') FROM MonthGroups ORDER BY MonthStart -- 拼接最终SQL语句 SET @SQL = 'SELECT ' + @FixedColumns + ', ' + @SumColumns + ' FROM [Table]' -- 执行动态SQL EXEC sp_executesql @SQL
说明
- 动态提取前12个固定字段，避免硬编码
- 自动识别所有日期格式的列，通过
TRY_CAST验证有效性 - 按月份分组，为每个月份生成对应的求和表达式，并按时间顺序命名为
Month1、Month2... - 最终拼接并执行查询，输出符合要求的结果
内容的提问来源于stack exchange，提问作者Kiran
相关产品推荐
相关产品推荐
