I have got this fairly complex query with multiple recursive members, which I need to run for a list of values, say for example Product Ids (the query outputs parent id along with all its child ids). How can I print out the result set for each of those values alongside that value itself.
Consider a scenario like below...
DECLARE @products table (prdid varchar(40));
Insert INTO @products values('A111'),('B111'),('A123'), ('Z456');
with rec_query as (
---anchor member
'
'
where pid IN @products
UNION ALL
---recursive members
'
'
)
I want something like
select prdid, rec_query.*
which would output something similar to below result
A111| its result set
B111| its result set
A123| its result set