Generate Columns based on two parameter in SQL Server

Hi Richa,

Refer below sample query.

SQL

DECLARE @startnum INT
SET @startnum = 2018
DECLARE @endnum INT
SET @endnum = 2022
;WITH gen AS (
    SELECT @startnum AS YEARS,0 'Amount'
    UNION ALL
    SELECT YEARS + 1,0 'Amount' FROM gen WHERE YEARS + 1 <= @endnum
)
SELECT * INTO #Temp FROM gen
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(YEARS) FROM (SELECT DISTINCT YEARS FROM #Temp) AS YEARS
PRINT @ColumnName
SET @DynamicPivotQuery = ';WITH CTE AS(SELECT * FROM #Temp)
SELECT DISTINCT ''AMOUNT'' '' '',' + @ColumnName + ' FROM CTE
PIVOT (MAX(AMOUNT) FOR AMOUNT IN('+@ColumnName+')) p'
EXEC(@DynamicPivotQuery)
DROP TABLE #Temp

Output