如何在SQL列标题中使用动态日期变量实现可变表头？

我正在创建一个展示物料预测数据的仪表板，该预测每周更新，希望周/月列的表头能动态显示对应日期或周数。已在SQL中声明时间周期变量统计对应时段的物料预测总量，但直接使用变量作为列别名（如AS @Week01）会出现语法错误，附上完整SQL代码寻求解决方法：

DECLARE @Today DATETIME = CAST(CAST(GETDATE() AS DATE) AS DATETIME);
DECLARE @CurrentWeekNumber INT = DATEPART(WEEK, GETDATE());
DECLARE @Week01 DATETIME = DATEADD(WEEK, DATEDIFF(WEEK, 0, @Today), 0);
DECLARE @Week02 DATETIME = DATEADD(WEEK, 1, @Week01);
DECLARE @Week03 DATETIME = DATEADD(WEEK, 1, @Week02);
DECLARE @Week04 DATETIME = DATEADD(WEEK, 1, @Week03);
DECLARE @Week05 DATETIME = DATEADD(WEEK, 1, @Week04);
DECLARE @Week06 DATETIME = DATEADD(WEEK, 1, @Week05);
DECLARE @Week07 DATETIME = DATEADD(WEEK, 1, @Week06);
DECLARE @Week08 DATETIME = DATEADD(WEEK, 1, @Week07);
DECLARE @Week09 DATETIME = DATEADD(WEEK, 1, @Week08);
DECLARE @Week10 DATETIME = DATEADD(WEEK, 1, @Week09);
DECLARE @Week11 DATETIME = DATEADD(WEEK, 1, @Week10);
DECLARE @Week12 DATETIME = DATEADD(WEEK, 1, @Week11);
DECLARE @Week13 DATETIME = DATEADD(WEEK, 1, @Week12);
DECLARE @Month01 DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, 0, @Week12) + 1, 0);
DECLARE @Month02 DATETIME = DATEADD(MONTH, 1, @Month01);
DECLARE @Month03 DATETIME = DATEADD(MONTH, 1, @Month02);
DECLARE @Month04 DATETIME = DATEADD(MONTH, 1, @Month03);
DECLARE @Month05 DATETIME = DATEADD(MONTH, 1, @Month04);
DECLARE @Month06 DATETIME = DATEADD(MONTH, 1, @Month05);
DECLARE @Month07 DATETIME = DATEADD(MONTH, 1, @Month06);
DECLARE @Month08 DATETIME = DATEADD(MONTH, 1, @Month07);
DECLARE @Month09 DATETIME = DATEADD(MONTH, 1, @Month08);

SELECT
  t0.ItemCode,
  t2.U_din AS "FP. Artikel Nr.",
  t2.ItemName AS "Beskrivelse",
  t1.OnHand AS "Lagerbeholdning",
  t1.IsCommited AS "Indv. Ordre",
  SUM(CASE WHEN t0.Date >= @Week01 AND t0.Date < @Week02 THEN t0.Quantity ELSE 0 END) AS 'Week 01', 
  SUM(CASE WHEN t0.Date >= @Week02 AND t0.Date < @Week03 THEN t0.Quantity ELSE 0 END) AS 'Week 02',
  SUM(CASE WHEN t0.Date >= @Week03 AND t0.Date < @Week04 THEN t0.Quantity ELSE 0 END) AS 'Week 03',
  SUM(CASE WHEN t0.Date >= @Week04 AND t0.Date < @Week05 THEN t0.Quantity ELSE 0 END) AS 'Week 04',
  SUM(CASE WHEN t0.Date >= @Week05 AND t0.Date < @Week06 THEN t0.Quantity ELSE 0 END) AS 'Week 05',
  SUM(CASE WHEN t0.Date >= @Week06 AND t0.Date < @Week07 THEN t0.Quantity ELSE 0 END) AS 'Week 06',
  SUM(CASE WHEN t0.Date >= @Week07 AND t0.Date < @Week08 THEN t0.Quantity ELSE 0 END) AS 'Week 07',
  SUM(CASE WHEN t0.Date >= @Week08 AND t0.Date < @Week09 THEN t0.Quantity ELSE 0 END) AS 'Week 08',
  SUM(CASE WHEN t0.Date >= @Week09 AND t0.Date < @Week10 THEN t0.Quantity ELSE 0 END) AS 'Week 09',
  SUM(CASE WHEN t0.Date >= @Week10 AND t0.Date < @Week11 THEN t0.Quantity ELSE 0 END) AS 'Week 10',
  SUM(CASE WHEN t0.Date >= @Week11 AND t0.Date < @Week12 THEN t0.Quantity ELSE 0 END) AS 'Week 11',
  SUM(CASE WHEN t0.Date >= @Week12 AND t0.Date < @Week13 THEN t0.Quantity ELSE 0 END) AS 'Week 12',
  SUM(CASE WHEN t0.Date >= @Week13 AND t0.Date < @Month01 THEN t0.Quantity ELSE 0 END) AS 'Week 13',
  SUM(CASE WHEN t0.Date >= @Month01 AND t0.Date < @Month02 THEN t0.Quantity ELSE 0 END) AS 'Month 01',
  SUM(CASE WHEN t0.Date >= @Month02 AND t0.Date < @Month03 THEN t0.Quantity ELSE 0 END) AS 'Month 02',
  SUM(CASE WHEN t0.Date >= @Month03 AND t0.Date < @Month04 THEN t0.Quantity ELSE 0 END) AS 'Month 03',
  SUM(CASE WHEN t0.Date >= @Month04 AND t0.Date < @Month05 THEN t0.Quantity ELSE 0 END) AS 'Month 04',
  SUM(CASE WHEN t0.Date >= @Month05 AND t0.Date < @Month06 THEN t0.Quantity ELSE 0 END) AS 'Month 05',
  SUM(CASE WHEN t0.Date >= @Month06 AND t0.Date < @Month07 THEN t0.Quantity ELSE 0 END) AS 'Month 06',
  SUM(CASE WHEN t0.Date >= @Month07 AND t0.Date < @Month08 THEN t0.Quantity ELSE 0 END) AS 'Month 07',
  SUM(CASE WHEN t0.Date >= @Month08 AND t0.Date < @Month09 THEN t0.Quantity ELSE 0 END) AS 'Month 08',
  SUM(CASE WHEN t0.Date >= @Month09 THEN t0.Quantity ELSE 0 END) AS 'Month 09'
FROM FCT1 t0
LEFT JOIN OITW t1 ON t1.ItemCode = t0.ItemCode
LEFT JOIN OITM t2 ON t2.ItemCode = t1.ItemCode
WHERE t0.AbsID = 7 AND t1.WhsCode = '01'
GROUP BY t0.ItemCode, t1.OnHand, t1.IsCommited, t2.ItemName, t2.U_din
ORDER BY t0.ItemCode ASC;

解决方法 #

方法1：使用动态SQL拼接列别名 #

SQL Server的静态SQL不支持直接用变量作为列别名，必须通过动态SQL拼接SQL语句字符串实现。核心是将动态日期/周数格式化为字符串，拼接到SELECT语句的列别名位置，再执行拼接后的SQL。

修改后的代码示例：

DECLARE @Today DATETIME = CAST(CAST(GETDATE() AS DATE) AS DATETIME);
DECLARE @CurrentWeekNumber INT = DATEPART(WEEK, GETDATE());
DECLARE @Week01 DATETIME = DATEADD(WEEK, DATEDIFF(WEEK, 0, @Today), 0);
DECLARE @Week02 DATETIME = DATEADD(WEEK, 1, @Week01);
DECLARE @Week03 DATETIME = DATEADD(WEEK, 1, @Week02);
DECLARE @Week04 DATETIME = DATEADD(WEEK, 1, @Week03);
DECLARE @Week05 DATETIME = DATEADD(WEEK, 1, @Week04);
DECLARE @Week06 DATETIME = DATEADD(WEEK, 1, @Week05);
DECLARE @Week07 DATETIME = DATEADD(WEEK, 1, @Week06);
DECLARE @Week08 DATETIME = DATEADD(WEEK, 1, @Week07);
DECLARE @Week09 DATETIME = DATEADD(WEEK, 1, @Week08);
DECLARE @Week10 DATETIME = DATEADD(WEEK, 1, @Week09);
DECLARE @Week11 DATETIME = DATEADD(WEEK, 1, @Week10);
DECLARE @Week12 DATETIME = DATEADD(WEEK, 1, @Week11);
DECLARE @Week13 DATETIME = DATEADD(WEEK, 1, @Week12);
DECLARE @Month01 DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, 0, @Week12) + 1, 0);
DECLARE @Month02 DATETIME = DATEADD(MONTH, 1, @Month01);
DECLARE @Month03 DATETIME = DATEADD(MONTH, 1, @Month02);
DECLARE @Month04 DATETIME = DATEADD(MONTH, 1, @Month03);
DECLARE @Month05 DATETIME = DATEADD(MONTH, 1, @Month04);
DECLARE @Month06 DATETIME = DATEADD(MONTH, 1, @Month05);
DECLARE @Month07 DATETIME = DATEADD(MONTH, 1, @Month06);
DECLARE @Month08 DATETIME = DATEADD(MONTH, 1, @Month07);
DECLARE @Month09 DATETIME = DATEADD(MONTH, 1, @Month08);

-- 定义动态SQL字符串变量
DECLARE @DynamicSQL NVARCHAR(MAX)

-- 拼接SQL语句，将动态日期/周数作为列别名
SET @DynamicSQL = N'
SELECT
  t0.ItemCode,
  t2.U_din AS "FP. Artikel Nr.",
  t2.ItemName AS "Beskrivelse",
  t1.OnHand AS "Lagerbeholdning",
  t1.IsCommited AS "Indv. Ordre",
  SUM(CASE WHEN t0.Date >= @Week01 AND t0.Date < @Week02 THEN t0.Quantity ELSE 0 END) AS ''' + 'Wk ' + CONVERT(VARCHAR(10), @Week01, 120) + ''', 
  SUM(CASE WHEN t0.Date >= @Week02 AND t0.Date < @Week03 THEN t0.Quantity ELSE 0 END) AS ''' + 'Wk ' + CONVERT(VARCHAR(10), @Week02, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Week03 AND t0.Date < @Week04 THEN t0.Quantity ELSE 0 END) AS ''' + 'Wk ' + CONVERT(VARCHAR(10), @Week03, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Week04 AND t0.Date < @Week05 THEN t0.Quantity ELSE 0 END) AS ''' + 'Wk ' + CONVERT(VARCHAR(10), @Week04, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Week05 AND t0.Date < @Week06 THEN t0.Quantity ELSE 0 END) AS ''' + 'Wk ' + CONVERT(VARCHAR(10), @Week05, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Week06 AND t0.Date < @Week07 THEN t0.Quantity ELSE 0 END) AS ''' + 'Wk ' + CONVERT(VARCHAR(10), @Week06, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Week07 AND t0.Date < @Week08 THEN t0.Quantity ELSE 0 END) AS ''' + 'Wk ' + CONVERT(VARCHAR(10), @Week07, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Week08 AND t0.Date < @Week09 THEN t0.Quantity ELSE 0 END) AS ''' + 'Wk ' + CONVERT(VARCHAR(10), @Week08, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Week09 AND t0.Date < @Week10 THEN t0.Quantity ELSE 0 END) AS ''' + 'Wk ' + CONVERT(VARCHAR(10), @Week09, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Week10 AND t0.Date < @Week11 THEN t0.Quantity ELSE 0 END) AS ''' + 'Wk ' + CONVERT(VARCHAR(10), @Week10, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Week11 AND t0.Date < @Week12 THEN t0.Quantity ELSE 0 END) AS ''' + 'Wk ' + CONVERT(VARCHAR(10), @Week11, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Week12 AND t0.Date < @Week13 THEN t0.Quantity ELSE 0 END) AS ''' + 'Wk ' + CONVERT(VARCHAR(10), @Week12, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Week13 AND t0.Date < @Month01 THEN t0.Quantity ELSE 0 END) AS ''' + 'Wk ' + CONVERT(VARCHAR(10), @Week13, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Month01 AND t0.Date < @Month02 THEN t0.Quantity ELSE 0 END) AS ''' + 'Month ' + CONVERT(VARCHAR(7), @Month01, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Month02 AND t0.Date < @Month03 THEN t0.Quantity ELSE 0 END) AS ''' + 'Month ' + CONVERT(VARCHAR(7), @Month02, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Month03 AND t0.Date < @Month04 THEN t0.Quantity ELSE 0 END) AS ''' + 'Month ' + CONVERT(VARCHAR(7), @Month03, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Month04 AND t0.Date < @Month05 THEN t0.Quantity ELSE 0 END) AS ''' + 'Month ' + CONVERT(VARCHAR(7), @Month04, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Month05 AND t0.Date < @Month06 THEN t0.Quantity ELSE 0 END) AS ''' + 'Month ' + CONVERT(VARCHAR(7), @Month05, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Month06 AND t0.Date < @Month07 THEN t0.Quantity ELSE 0 END) AS ''' + 'Month ' + CONVERT(VARCHAR(7), @Month06, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Month07 AND t0.Date < @Month08 THEN t0.Quantity ELSE 0 END) AS ''' + 'Month ' + CONVERT(VARCHAR(7), @Month07, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Month08 AND t0.Date < @Month09 THEN t0.Quantity ELSE 0 END) AS ''' + 'Month ' + CONVERT(VARCHAR(7), @Month08, 120) + ''',
  SUM(CASE WHEN t0.Date >= @Month09 THEN t0.Quantity ELSE 0 END) AS ''' + 'Month ' + CONVERT(VARCHAR(7), @Month09, 120) + '''
FROM FCT1 t0
LEFT JOIN OITW t1 ON t1.ItemCode = t0.ItemCode
LEFT JOIN OITM t2 ON t2.ItemCode = t1.ItemCode
WHERE t0.AbsID = 7 AND t1.WhsCode = ''01''
GROUP BY t0.ItemCode, t1.OnHand, t1.IsCommited, t2.ItemName, t2.U_din
ORDER BY t0.ItemCode ASC;'

-- 执行动态SQL，传递变量参数
EXEC sp_executesql @DynamicSQL, 
    N'@Week01 DATETIME, @Week02 DATETIME, @Week03 DATETIME, @Week04 DATETIME, @Week05 DATETIME,
      @Week06 DATETIME, @Week07 DATETIME, @Week08 DATETIME, @Week09 DATETIME, @Week10 DATETIME,
      @Week11 DATETIME, @Week12 DATETIME, @Week13 DATETIME, @Month01 DATETIME, @Month02 DATETIME,
      @Month03 DATETIME, @Month04 DATETIME, @Month05 DATETIME, @Month06 DATET