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?
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
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
);
