SQL Server动态生成按年份命名季度列的查询实现
动态查询实现方案
无需硬编码年份的动态透视查询,完全兼容原有静态查询的字段逻辑和列命名规则,自动识别业务表内的全量有效年份。
核心实现代码
USE [My_Table] GO DECLARE @max_intyear INT, @max_byteQuarter INT, @min_intyear INT, @dynamic_sql NVARCHAR(MAX), @column_defs NVARCHAR(MAX); -- 自动提取表内的年份、季度极值,替代手动硬编码的起始值 SELECT @max_intyear = MAX(intyear), @max_byteQuarter = MAX(byteQuarter), @min_intyear = MIN(intyear) FROM [My_Table].[dbo].[PW_Projected]; -- 动态生成全量年份、季度对应的统计列定义 SELECT @column_defs = STRING_AGG( CONCAT( N'MAX(CASE WHEN byteQuarter = ', q.quarter, N' AND intYear = ', y.year, N' THEN curUnits END) AS ', QUOTENAME(CONCAT(y.year, N'_Units(ID) Q', q.quarter)) ), N',' ) FROM ( -- 自动去重提取表内所有出现过的有效年份 SELECT DISTINCT intyear AS year FROM dbo.PW_Projected ) y CROSS JOIN ( -- 固定覆盖4个季度的统计逻辑 SELECT 1 AS quarter UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) q -- 按年份、季度升序排列,保证列顺序和原有硬编码逻辑完全一致 ORDER BY y.year, q.quarter; -- 拼接完整的查询逻辑 SET @dynamic_sql = N' SELECT intMedicine, intForm, MAX(CASE WHEN intyear = @max_intyear AND byteQuarter = @max_byteQuarter THEN curprice END) AS curprice, ' + @column_defs + N' INTO Test FROM dbo.PW_Projected WHERE intYear >= @min_intyear GROUP BY intMedicine, intForm'; -- 参数化执行动态SQL EXEC sp_executesql @stmt = @dynamic_sql, @params = N'@max_intyear INT, @max_byteQuarter INT, @min_intyear INT', @max_intyear = @max_intyear, @max_byteQuarter = @max_byteQuarter, @min_intyear = @min_intyear;
适配说明
- 代码使用
STRING_AGG做字符串拼接,适配SQL Server 2017及以上版本;若为更早版本,可将拼接列定义的部分替换为FOR XML PATH的传统拼接写法。 - 列名通过
QUOTENAME自动包裹双引号,避免特殊字符引发的语法错误。 - 动态SQL采用参数化传值,杜绝SQL注入风险,同时减少字符串拼接的格式问题。
- 当表内新增后续年份的数据时,无需修改任何查询代码,会自动生成对应年份4个季度的统计列。
内容的提问来源于stack exchange,提问作者Abed
相关产品推荐
相关产品推荐

