How to use a while loop within a select query-CodePudding

I'm trying to create a stored procedure that return results that are based on year the user inputs. Say the user chooses a start year of 2016 and an end year of 2018, the stored procedure will loop though those years to return results. However, I only need the select to loop through a few columns for this.

For example, I'm looking for something similar to the logic behind the below.

SELECT 
    column A, column B
    WHILE (@CurrentYr <= @MaxYr)
    BEGIN
        ,CASE WHEN(Year = @CurrentYr THEN, columnC)
        SET @CurrentYr = @CurrentYr   1
    END
FROM 
    Table1

which would return columns

ColumnA, ColumnB, 2016's ColumnC, 2017's ColumnC, 2018's Column8

Is this possible?

Thanks

CodePudding user response：

Here is a code sample that creates a temporary table "#MyTemp" with the base columns and then has a loop to add additional columns for the years based on your current year and maximum year variables. After each column is added with the dynamic SQL you can then update that column with data using additional Dynamic SQL.

DECLARE @YearCurrent int
DECLARE @YearMax int
DECLARE @YearCounter INT
DECLARE @ColumnName VARCHAR(20)
DECLARE @Sql varchar(100)

SELECT @YearCurrent = 2018
SELECT @YearMax = 2021

SELECT SettingID AS ColumnA, SettingValue AS ColumnB
INTO #MyTemp
FROM ApplicationSetting

SELECT @YearCounter = @YearCurrent
WHILE @YearCounter <= @YearMax
BEGIN
   SELECT @ColumnName = 'Year_'   CAST(@YearCounter AS VARCHAR(20))
   SELECT @SQL = 'ALTER TABLE #MyTemp ADD ['   @ColumnName   '] VarChar(100) NULL '
   PRINT @SQL
   EXEC ( @SQL )

   SELECT @SQL = 'UPDATE #MyTemp SET '   @ColumnName   '= OrderHeader.SomeColumn FROM OrderHeader WHERE #MyTemp.ColumnA = OrderHeader.ColumnA'
   PRINT @SQL
   EXEC ( @SQL )

   SELECT @YearCounter = @YearCounter   1
END

SELECT * FROM #MyTemp

I am not sure what you want to put in the year columns but the update statement gives you the basic idea and you can adjust the update statement as needed.

Sample Output

CodePudding user response：

You cannot use WHILE within a SELECT statement. However, here is a pattern that may help you build a dynamic one:

DECLARE @CurrentYr int = 2019, @MaxYr int = 2021, @sql varchar(MAX);

SELECT @sql = 'SELECT columnA, columnB, '   (
    SELECT STRING_AGG (
            CASE YearIncrement
                WHEN 0 THEN CONCAT ( 'columnC AS Year_', @CurrentYr   YearIncrement )
                WHEN 1 THEN CONCAT ( 'columnD AS Year_', @CurrentYr   YearIncrement )
                WHEN 2 THEN CONCAT ( 'columnE AS Year_', @CurrentYr   YearIncrement )
                -- WHEN...etc
            END
            , ', '
        ) AS cols
    FROM (
        SELECT
            ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) - 1 AS YearIncrement
        FROM STRING_SPLIT ( ( SELECT REPLICATE ( ',', @MaxYr - @CurrentYr ) ), ',' )
    ) AS x
)
      ' FROM Table1;';

PRINT @sql;

PRINTS

SELECT columnA, columnB, columnC AS Year_2019, columnD AS Year_2020, columnE AS Year_2021 FROM Table1;

By using a combination of

REPLICATE (creates a list of commas based on the difference in years)
STRING_SPLIT (splits said comma list into rows)
ROW_NUMBER (provides a number (-1 to start the increment at zero) to add to each year over said rows)
STRING_AGG (combines them all back into a comma-delimited list)

we can create a dynamic SQL statement that may be executed. Unfortunately, I am unaware of a workaround for the CASE conditions as your column names will vary.