Generate Columns based on two parameter in SQL Server

Last Reply 6 months ago By dharmendr

Posted 6 months ago

How can I change below Sql Select query result/output into column ?

DECLARE @startnum INT=2018
DECLARE @endnum INT=2022
;
WITH gen AS (
    SELECT @startnum AS YEARS
    UNION ALL
    SELECT YEARS + 1 FROM gen WHERE YEARS + 1 <= @endnum
)
SELECT YEARS
FROM gen as AA

Actually, I want to create a Stored Procedure which Shall return the output like below:

  2018 2019 2020 2021 2022

Thank you in advance.

You are viewing reply posted by: dharmendr 6 months ago.
Posted 6 months ago

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

  2018 2019 2020 2021 2022
AMOUNT NULL NULL NULL NULL NULL