Union of multiple selects based on different parameters values in sql server

Question

I would like to have the following functionality in sql server: (PSEUDOCODE)

FOR @PARAM IN (value1, value2, ..., valueN)
     UNION(select @PARAM as window_id, variable1,*  from TABLE where variable1<=@PARAM)

I mean, each value of @PARAM inidicates a different window, for which I would like to run the above select, and then make an union of all of them. ¿Is there any way to do this in a convenient way in sql server?

Answer 1

I would recommend to store this @param value in a table and use that as input in a while loop

FOR loop is not available in sql server.

create table input_value (id int identity(1,1),val int)

insert into input_value(val)
values(<your param values>)

--create a new empty table with the same structure as your source table to store the run time result set

select 1 as int,variable1 as new_v2, * into #table from  TABLE where variable1 <> variable1

declare @i int = 1, @end int = 0, @param int

select @end = max(id) from input_value
while @end>= @i
begin
select @param = val from input_value where id = @i
insert into #table
select @PARAM as window_id, variable1,*  from TABLE where variable1<=@PARAM

set @i  = @i+1
end
select * from #table

Note: every time the input_value table has to be truncated to run this code, truncate will reset the identity to 1 as well as delete the old records