Intersection of data in oracle

Question

I have two columns in a table.One represents work and the other represents date on which that needs to perform..I need the output as a minimum possible combination. For the below records I am expecting the output as W1,W2,W3 on 16th and 17th . And W4,W5 on 20th. Is there anyway to achieve this in a query?

W1  15-06-2015
W1  16-06-2015
W1  17-06-2015
W2  16-06-2015
W2  17-06-2015
W2  18-06-2015
W3  16-06-2015
W3  17-06-2015
W4  20-06-2015
W4  21-06-2015
W5  20-06-2015

Answer 1

select Date, wm_concat(work) work_concat from table group by Date 

Then you can order by length(work_concat) if that gives you the output you're looking for. From what I can gather, you want the maximum intersection of work, correct?

However, if you want to just be given the maximum intersection and no other records, this will do that:

    select to_char(work_concat), dateFieldb from (

select rank() over (partition by work order by length(work_concat) desc) rn, c.* from (
select dateFieldb, work_Concat, a.work from work a
inner join (
select DateField dateFieldb, wm_concat(work) work_concat from work a group by DateField
  ) b on b.work_concat like ('%' || a.work || '%')
  ) c

) where rn = 1
group by to_char(work_concat), dateFieldb


TO_CHAR(WORK_CONCAT)  DATEFIELDB
--------------------------------
W1,W3,W2              16-JUN-15  
W1,W3,W2              17-JUN-15  
W4,W5                 20-JUN-15 

Answer 2

In Oracle 11g use function listagg() twice:

^{SQLFiddle demo}

select listagg(wdate, ', ') within group (order by works) dates, works
  from (
    select wdate, listagg(wid, ', ') within group (order by wid) works
      from data group by wdate)
  group by works;

... or if you are interested only in combinations containing more than one common value:

select dates, works from (
    select listagg(wdate, ', ') within group (order by works) dates, works, 
        cnt1, count(1) cnt2
      from (
        select wdate, listagg(wid, ', ') within group (order by wid) works,
            count(1) cnt1
          from data group by wdate)
      group by works, cnt1)
  where cnt1 >1 or cnt2>1