oracle11g - Oracle Intersection of rows in a Table -

hi have table 2 columns (wid,dt) data..

wid dt -------   10   11   12 b   10 b   11 c   10 c   13 

if pass a,b in input should output 10 , 11. intersection of dt. if pass a,b,c in input should 10 output. if pass input 10,11,12 output.. input dynamic depends on conditions ui. how achieve in query...

here's way doesn't require add intersect each item in list pass in parameter. can't guarantee peform on large data sets, however, you'd have test data!

this first example shows results mimics effect of multiple values of parameter being passed in @ same time (mainly show results of different test cases mentioned):

with sample_data (select 'a' wid, 10 dt dual union                      select 'a' wid, 11 dt dual union                      select 'a' wid, 12 dt dual union                      select 'b' wid, 10 dt dual union                      select 'b' wid, 11 dt dual union                      select 'c' wid, 10 dt dual union                      select 'c' wid, 13 dt dual),           params (select 'a, b, c' val dual union                      select 'a, b' val dual union                      select 'a, c' val dual union                      select 'b, c' val dual union                      select 'a' val dual union                      select 'b' val dual union                      select 'c' val dual),     pivot_params (select val,                             trim(regexp_substr(val, '[^,]+', 1, level)) sub_val,                             regexp_count(val||',', ',') num_vals                        params                      connect prior val = val                                 , level <= regexp_count(val||',', ',')                                 , prior dbms_random.value not null),          results (select sd.*,                             pp.*,                             count(distinct wid) on (partition pp.val, sd.dt) cnt_of_distinct_wid_per_val                        sample_data sd                             inner join pivot_params pp on (sd.wid = pp.sub_val)) select distinct val param,                 dt   results  num_vals = cnt_of_distinct_wid_per_val order 1, 2;   param           dt ------- ----------               10               11               12 a, b            10 a, b            11 a, b, c         10 a, c            10 b               10 b               11 b, c            10 c               10 c               13 

this second example closer you'd need pass in parameter single value - you'd not need sample_data subquery (you'd use tablename in place of sample_data used in main query), , you'd have replace :param parameter name you're using in code, can see you'd need amend working in code:

with sample_data (select 'a' wid, 10 dt dual union                      select 'a' wid, 11 dt dual union                      select 'a' wid, 12 dt dual union                      select 'b' wid, 10 dt dual union                      select 'b' wid, 11 dt dual union                      select 'c' wid, 10 dt dual union                      select 'c' wid, 13 dt dual),      -- end of mimicking data      pivot_param (select :param val,                             trim(regexp_substr(:param, '[^,]+', 1, level)) sub_val,                             regexp_count(:param||',', ',') num_vals                        dual                      connect level <= regexp_count(:param||',', ',')),          results (select sd.*,                             pp.*,                             count(distinct wid) on (partition pp.val, sd.dt) cnt_of_distinct_wid_per_val                        sample_data sd                             inner join pivot_param pp on (sd.wid = pp.sub_val)) select distinct val param,                 dt   results  num_vals = cnt_of_distinct_wid_per_val order 1, 2; 

eta: way works is: first, turn list-as-a-parameter dummy table, 1 row per item in list, along count of how many items passed in. (nb. haven't taken consideration case have entered same item twice in query - you'd have amend way identify count of items in pivot_params subquery (probably using count(distinct(...) on (...)) , making sure output distinct.)

once have converted parameter list table, can join table containing data (in queries above, sample_data subquery), , find out how many unique wids there per dt. if count same count of items in parameter list, know items matched.