SQL Server 2012 - Dynamic Grouping

Question

I have a table in which there is a single column [combination] which contains each unique combinations of the letters from AAA - ZZZ. There are 17,576 records.

Now, what I cannot figure out is, based on a dynamic grouping number, how would I determine the unique combinations of combinations? For example, if the grouping is 2, I would expect results of the following:

• Group 1 - AAA - MZZ
• Group 2 - NAA - ZZZ

Additionally, each Group will have a dynamic number of Workers assigned. So, if I enter 2 employees for Group 1, I would expect Results of something like

• Employee 1 - Group 1 - AAA - GJD
• Employee 2 - Group 1 - GJE- MZZ

Is any of this possible and if so, can anyone guide me in the right direction?

Thanking you in advance.

Answer 1

with a
as
(
    select char(64 + ns.n) as lt
    from 
    (
        select top (26) row_number() over(order by (select null)) as n
        from sys.columns
    ) as ns
)
select a.lt + b.lt + c.lt as combo
into #a
from a as a
cross join a as b
cross join a as c;

create unique clustered index ucxcombo on #a(combo)
go


declare @groups int = 100, 
        @employeespergroup int = 15;

select @groups * @employeespergroup as totalrows;

select 
    groupno,  subgroup, subgroup + (groupno-1)* @employeespergroup as employeeordinal, min(combo) as fromcombo, max(combo) as tocombo
from
(
    --ntile for employees within a group
    select combo, groupno, ntile(@employeespergroup) over(partition by groupno order by combo) subgroup
    from
    (
        --ntile for groups
        select *, ntile(@groups) over(order by combo) as groupno
        from #a
    ) as s
) as e
group by groupno, subgroup
order by groupno, subgroup;

Answer 2

I discovered NTILE which breaks the records into buckets...

Now, I need to figure out how to weight those buckets, but that's another topic.