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

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?

CodePudding user response：

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

CodePudding user response：

You can just join against a VALUES virtual table

SELECT
  v.window_id,
  t.variable1
FROM (VALUES
    (@value1),
    (@value2)  -- etc...
) v(window_id)
JOIN [TABLE] t ON t.variable1 <= v.window_id;

I'd advise you though to use a Table-Valued Parameter or a table variable to do this join though. And if you have these parameters in an existing table you can join to that instead.