SQL – find all combinations

From a dataset like this, I need to get all possible combinations of cases in the same room so that no case overlaps another.

room        case        start               end
a           1           2019-11-27 09:00    2019-11-27 10:15
a           2           2019-11-27 10:30    2019-11-27 12:00
a           3           2019-11-27 12:00    2019-11-27 12:30
b           4           2019-11-27 08:30    2019-11-27 10:30
b           5           2019-11-27 10:00    2019-11-27 12:00
b           6           2019-11-27 11:00    2019-11-27 12:20

The expected result is

room        combination         cases   
a           1                   1
a           2                   1,2
a           3                   1,2,3
a           4                   2
a           5                   2,3
a           6                   3
b           1                   4
b           2                   4,6
b           3                   5
b           4                   6

I am able to get single line results of what case can be combined with which other case like:

room        case        combinewithcase
a           1           2
a           1           3
a           2           1
a           2           3
a           3           1
a           3           2
b           4           6
b           6           4

And I’ve tried some recursions but I am at a loss nowhere near to getting the type of results I need, I would appreciate any guidance anyone can share.

Answer

Here is one method using a string aggregation. To get the overlaps by time:

select rd.room, rd.dte,
       (select string_agg(t2.case, ',') within group (order by t2.case)
        from t t2
        where t2.room = t.room and
              t2.start <= t.dte and
              t2.end > t.dte
       ) as cases
from ((select room, start as dte from t
      ) union -- on purpose to remove duplicates
      (select room, end from t
      )
     ) rd;

You can then use this as a subquery/CTE to enumerate the combinations:

select room, cases, count(*),
       row_number() over (partition by room order by combination) as combination
from (select rd.room, rd.dte,
             (select string_agg(t2.case, ',') within group (order by t2.case)
              from t t2
              where t2.room = t.room and
                    t2.start <= t.dte and
                    t2.end > t.dte
             ) as cases
      from ((select room, start as dte from t
            ) union -- on purpose to remove duplicates
            (select room, end from t
            )
           ) rd
     ) rd
group by room, cases
order by room, cases;

EDIT:

In earlier versions of SQL Server, you can use the XML method for string aggregation:

select room, cases, count(*),
       row_number() over (partition by room order by combination) as combination
from (select rd.room, rd.dte,
             stuff( (select concat(',', t2.case)
                     from t t2
                     where t2.room = t.room and
                           t2.start <= t.dte and
                           t2.end > t.dte
                     order by t2.case
                     for xml path
                    ), 1, 1, '')
             ) as cases
      from ((select room, start as dte from t
            ) union -- on purpose to remove duplicates
            (select room, end from t
            )
           ) rd
     ) rd
group by room, cases
order by room, cases;