I have several columns. Each has it’s own grouping of consecutive sequences. I would like to create a parent group based on those smaller groups. The following is a small example of what I am attempting to accomplish. The data represents Teams that can belong to different Departments, that can belong to different Companies.
Given the table:
<code>company_id department_id team_id
1 101 10
1 101 11
1 101 12
1 102 10
1 102 11
1 102 12
2 101 10
2 101 11
2 101 12
2 102 10
2 102 11
2 102 12
</code>
<code>company_id department_id team_id
1 101 10
1 101 11
1 101 12
1 102 10
1 102 11
1 102 12
2 101 10
2 101 11
2 101 12
2 102 10
2 102 11
2 102 12
</code>
company_id department_id team_id
1 101 10
1 101 11
1 101 12
1 102 10
1 102 11
1 102 12
2 101 10
2 101 11
2 101 12
2 102 10
2 102 11
2 102 12
The desired results:
<code>company_ids department_ids team_ids
1-2 101-102 10-12
</code>
<code>company_ids department_ids team_ids
1-2 101-102 10-12
</code>
company_ids department_ids team_ids
1-2 101-102 10-12
As you can see, this represents the data in a more concise manner.
Likewise, I also need to account for gaps. Because not all Teams will overlap all Departments; and not all Departments will overlap all Companies. Given this example table:
<code>company_id department_id team_id
1 101 10
1 101 11
1 101 12
1 102 10
1 102 11
1 102 12
2 101 10
2 101 11
2 101 12
2 102 10
2 102 11
<last record removed>
</code>
<code>company_id department_id team_id
1 101 10
1 101 11
1 101 12
1 102 10
1 102 11
1 102 12
2 101 10
2 101 11
2 101 12
2 102 10
2 102 11
<last record removed>
</code>
company_id department_id team_id
1 101 10
1 101 11
1 101 12
1 102 10
1 102 11
1 102 12
2 101 10
2 101 11
2 101 12
2 102 10
2 102 11
<last record removed>
Desired results:
<code>company_ids department_ids team_ids
1-1 101-102 10-12
2-2 101-101 10-12
2-2 102-102 10-11
</code>
<code>company_ids department_ids team_ids
1-1 101-102 10-12
2-2 101-101 10-12
2-2 102-102 10-11
</code>
company_ids department_ids team_ids
1-1 101-102 10-12
2-2 101-101 10-12
2-2 102-102 10-11
The precedence of the resulting groups is not extremely important. For example, the previous results are synonymous with:
<code>company_ids department_ids team_ids
1-2 101-102 10-11
1-1 101-102 12-12
2-2 101-101 12-12
</code>
<code>company_ids department_ids team_ids
1-2 101-102 10-11
1-1 101-102 12-12
2-2 101-101 12-12
</code>
company_ids department_ids team_ids
1-2 101-102 10-11
1-1 101-102 12-12
2-2 101-101 12-12
Any help would be greatly appreciated! Thank you in advance!