Append a variable to the list of column headers in pivot table

Question

I have the following code for my pivot table:

Create table #CustView
(
  Customer_Code       VARCHAR(150),
  Part_No             VARCHAR(150),
  Invoice_Date        DATETIME,
  Quantity            FLOAT,
)
Insert into #CustView
Select 
max(Customer_Code) as Customer_Code
,Part_No
,max(datename(m,Invoice_Date)+''+cast(datepart(yyyy,Invoice_Date) as varchar)) as Invoice_Date
,Round(sum(Quantity),2,0) as Quantity
from
 #CustInfo as ci
where
 ci.Shipper_Line_Key <> 'NULL'
group by year(Invoice_Date), month(Invoice_Date), Part_No

//---------pivoting table and selecting the result view with below code --------

SELECT 
  *
FROM #CustView AS cstvw  
PIVOT 
  (   
    AVG(Quantity) FOR Invoice_Date IN 
      (
        January2023, February2023, March2023, April2023, May2023, June2023, 
        July2023, August2023, September2023, October2023, November2023, December2023
      )
  ) AS PivotTable
ORDER BY Customer_Code ASC, Part_No ASC

I want to append an integer "@Year" variable to the column header so that they change based on user input. In short, I'm trying the following code:

SELECT 
  *
FROM #CustView AS cstvw  
PIVOT 
  (
    AVG(Quantity) FOR Invoice_Date IN 
      (
        January'+@Year+', February'+@Year+', March'+@Year+', April'+@Year+', May'+@Year+', 
        June'+@Year+', July'+@Year+', August'+@Year+', September'+@Year+', 
        October'+@Year+', November'+@Year+', December'+@Year+'
      )
  ) AS PivotTable
ORDER BY Customer_Code ASC, Part_No ASC 

However, the above code is giving me an error. Can anyone please help me with this?

Answer 1

Untested, but give this a shot

Declare @SQL varchar(max) = '
SELECT 
  *
FROM #CustView AS cstvw  
PIVOT 
  (
    AVG(Quantity) FOR Invoice_Date IN 
      (
        January[getYear], February[getYear], March[getYear], April[getYear], May[getYear], 
        June[getYear], July[getYear], August[getYear], September[getYear], 
        October[getYear], November[getYear], December[getYear]
      )
  ) AS PivotTable
ORDER BY Customer_Code ASC, Part_No ASC 
'

Set @SQL = replace(@SQL,'[getYear]','2023')

Exec(@SQL)