How can I evenly distribute rows in one table to rows in another table in TSQL?

Question

I am trying to figure out a SQL query that will distribute or assign records from one table to another table evenly. The best way to explain it is via a contrived example.

Let's say I have a table of employees, and I want to assign each a color out of a table of colors.

I want to make sure that the colors are evenly distributed, but there is no property of an employee that would predict which color they would receive.

Employees:

Sam
John
Jack
April
Sonny
Jill
Jane

Colors:

Red
Green
Blue

Result:

Sam - Red
John - Green
Jack - Blue
April - Red
Sonny - Green
Jill - Blue
Jane - Red

How can I construct this join in TSQL?

Answer 1

I know the question is about SQLServer, but for those who are interested in a solution without NTILE and just using row_number:

-- (PostgreSQL syntax, but can be easly adapted to any DB)

with 

-- "uses" a dummy colors table just for testing
colors as 
  (select 'Red' as color union all
   select 'Green'        union all
   select 'Blue' 
  )
,

-- "uses" a dummy employees table just for testing
employees as 
  (select 'John' as employee union all
   select 'Jack'             union all
   select 'April'            union all
   select 'Sonny'            union all
   select 'Jill'             union all
   select 'Jane'             
  )
,

-- here's the trick: 

-- first we define a code_num as row_number for each color
c as
(select *, 
        row_number() over() as color_num 
   from colors
),

-- and then we define a code_num for each employee as 
-- ((row_number-1) "mod" (colors' table count)) +1
e as 
(select *, 
        (((row_number() over())-1) % (select count(*) from colors))+1 as color_num 
   from employees
)

-- get both tables joined by color_num   
select e.employee, 
       c.color
  from e 
  join c on c.color_num = e.color_num

Output:

employee  color
---------------
    John    Red
    Jack  Green
   April   Blue
   Sonny    Red
    Jill  Green
    Jane   Blue

Answer 2

I think you want to use the NTILE function with an argument that is the number of colors. Like this:

;WITH Employees AS 
    (SELECT EmployeeName, NTILE(3) OVER (ORDER BY EmployeeName ASC) as ColorNumber FROM (values 
        ('John'),('Jack'),('April'),('Sonny'),('Jill'),('Jane')) as e(EmployeeName)
    ),
    Colors AS 
    (SELECT ColorName, ROW_NUMBER() OVER (ORDER BY ColorName ASC) as ColorNumber FROM (values 
        ('Red'),('Green'),('Blue')) as c(ColorName)
    )
SELECT EmployeeName, ColorName
FROM Employees
INNER JOIN Colors ON Employees.ColorNumber = Colors.ColorNumber