SQL Server: Generate WHERE clause from resultset ids?-CodePudding

I have a query that produces the following resultset:

ID (text)    VAL
 1           A
 2           B
 3           C

I want to generate a WHERE clause from the resultset IDs:

where id in ('1','2','3')

With Oracle, I can use the following query to generate a WHERE clause:

with a as (

  <my_query>

)
select 'where id in ('
  || listagg(id, ',') within group(order by id)
  || ')' as where_clause
from a

Is there a way to do the same sort of thing using SQL Server?

CodePudding user response：

Just try this.

with a as (

  <my_query>

)
SELECT 'WHERE id IN ('  
  STRING_AGG(id, ',') WITHIN GROUP(ORDER BY id) 
    ')' as where_clause
FROM a

then you will get WHERE id IN (1,2,3)

If you want to get WHERE id IN ('1','2','3') then you can change like following

SELECT 'WHERE id IN ('''  
      STRING_AGG(id, ''',''') WITHIN GROUP(ORDER BY id) 
        ''')' as where_clause
FROM a

CodePudding user response：

You can just use a normal IN

with a as (

  <my_query>

),
b as (

  <Other Query>

)
select *
from b
where b.id in (
  select a.id
  from a
);