Zip/repeat join?

Question

Let's say I have a simple table of documents with a type column:

Documents
Id  Type
1   A
2   A
3   B
4   C
5   C
6   A
7   A
8   A
9   B
10  C

Users have permissions to access different types of documents:

Permissions
Type    User
A       John
A       Jane
B       Sarah
C       Peter
C       John
C       Mark

And I need to distribute those documents among the users as tasks:

Tasks
Id  T DocId UserId
1   A   1   John
2   A   2   Jane
3   B   3   Sarah
4   C   4   Peter
5   C   5   John
6   A   6   John
7   A   7   Jane
8   A   8   John
9   B   9   Sarah
10  C   10  Mark

How do I do that? How do I get the Tasks?

Answer 1

You can enumerate the rows and then use modulo arithmetic for the matching:

with d as (
      select d.*,
             row_number() over (partition by type order by newid()) as seqnum,
             count(*) over (partition by type) as cnt
      from documents d
     ),
      u as (
       select u.*,
              row_number() over (partition by type order by newid()) as seqnum,
              count(*) over (partition by type) as cnt
       from users u
      )
select d.*
from d join
     u
     on d.type = u.type and
        u.seqnum = (d.seqnum % u.cnt) + 1