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.

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;
```
7 People found this is helpful