sql - Joining and Repeating Rows -

i have requirement repeat rows values in other tables. ex:

order table order 1 2 3 4 5 6  dept table  dept    person   p1   p2 b   p3 b   p4 b   p5 c   p6 c   p7 c   p8 c   p9  output expected dept    person  order   p1  1   p2  2   p1  3   p2  4   p1  5   p2  6 b   p3  1 b   p4  2 b   p5  3 b   p3  4 b   p4  5 b   p5  6 c   p6  1 c   p7  2 c   p8  3 c   p9  4 c   p6  5 c   p7  6 

any ideas on how this? kept order simplicity need not in sequence; assume of date or varchar!

here's 1 way it:

select dept, person, [order] (select dept, person,             row_number() on (partition dept                                 order person) rn,             count(*) on (partition dept) cnt       dept ) t                             cross join [order] rn = ([order] - 1) % cnt + 1 order dept, [order], person 

this sort of brute force solution: every single combination between order , dept tables , use window functions selectively filter rows out of cartesian set.

demo here

edit: (credit goes @giorgi)

the same result can achieved simple join:

select dept, person, [order] (select dept, person,             row_number() on (partition dept                                 order person) rn,             count(*) on (partition dept) cnt       dept ) t                             inner join [order] on rn = ([order] - 1) % cnt + 1 order dept, [order], person 

the latter solution more efficient.

demo here