I have a table with many rows and many numbered columns, similar to this one:

I need to preform a dynamic query on all the columns from "name" 1 to "name" n, while n is a number that I receive from somewhere else.

Is there a way to implement a loop into the query that will preform this task for me? Or do I need to write an external script that will build the query string? I'm using SQL server

Edit I'm using SQL 19, not 05 as the a tag suggested, and I cant change the structure of the table.

CodePudding user response：

I agree with Larnu, these stage times should be in rows, not columns. But since you're stuck with a bad design:

DECLARE @NumberOfColumns int = 30;

DECLARE @Columns nvarchar(max) = N'', 
        @sql     nvarchar(max);

SELECT @Columns = 
STRING_AGG(QUOTENAME(CONCAT(N'stage ', n, ' time')), 
  N','   char(13)   char(10))
FROM 
(
  SELECT TOP (@NumberOfColumns) n = ROW_NUMBER() OVER (ORDER BY @@SPID)
  FROM sys.all_columns 
  ORDER BY [object_id]
) AS x;

SELECT @sql = N'SELECT batch, 
'   @Columns   N' FROM dbo.tablename;';

SELECT @sql;
-- EXEC sys.sp_executesql @sql;

Output:

SELECT batch, 
[stage 1 time],
[stage 2 time],
[stage 3 time],
[stage 4 time],
[stage 5 time],
[stage 6 time],
[stage 7 time],
[stage 8 time],
[stage 9 time],
[stage 10 time],
[stage 11 time],
[stage 12 time],
[stage 13 time],
[stage 14 time],
[stage 15 time],
[stage 16 time],
[stage 17 time],
[stage 18 time],
[stage 19 time],
[stage 20 time],
[stage 21 time],
[stage 22 time],
[stage 23 time],
[stage 24 time],
[stage 25 time],
[stage 26 time],
[stage 27 time],
[stage 28 time],
[stage 29 time],
[stage 30 time] FROM dbo.tablename;

• Example db<>fiddle

CodePudding user response：

below if it helps you, i attach a link loop through sql columns

or you can use t-sql loop through dynamically based on the variable you pass loop dynamically

google key terms if it helps you: t-sql, script, loop