SQL Server: Generate WHERE clause from resultset ids?

Question

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?

---

Related: Oracle: Generate WHERE clause from resultset ids?

Answer 1

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