Joining and Repeating Rows

Question

I have a requirement to repeat rows for the values in other tables. For ex:

Order table
Order
1
2
3
4
5
6

Dept table

Dept    Person
A   P1
A   P2
B   P3
B   P4
B   P5
C   P6
C   P7
C   P8
C   P9

Output expected
Dept    Person  Order
A   P1  1
A   P2  2
A   P1  3
A   P2  4
A   P1  5
A   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 to do this? I kept order just for simplicity but it need not be in sequence; Assume it be of a date or varchar!

Answer 1

Here's one way to do it:

SELECT Dept, Person, [Order]
FROM (SELECT Dept, Person,
            ROW_NUMBER() OVER (PARTITION BY Dept 
                               ORDER BY Person) AS rn,
            COUNT(*) OVER (PARTITION BY Dept) AS cnt
      FROM Dept ) t                            
CROSS JOIN [Order]
WHERE rn = ([Order] - 1) % cnt + 1
ORDER BY Dept, [Order], Person

This is some sort of brute force solution: You get every single combination between Order and Dept tables and use window functions to selectively filter rows out of this cartesian set.

Demo here

EDIT: (credit goes to @Giorgi)

The same result can be achieved with a simple JOIN:

SELECT Dept, Person, [Order]
FROM (SELECT Dept, Person,
            ROW_NUMBER() OVER (PARTITION BY Dept 
                               ORDER BY Person) AS rn,
            COUNT(*) OVER (PARTITION BY Dept) AS cnt
      FROM Dept ) t                            
INNER JOIN [Order] ON rn = ([Order] - 1) % cnt + 1
ORDER BY Dept, [Order], Person

The latter solution is definitely more efficient.

Demo here

Answer 2

Here is another one, I think also interesting:

DECLARE @o TABLE ( id INT )
INSERT  INTO @o
VALUES  ( 1 ),( 2 ),( 3 ),( 4 ),( 5 ),( 6 )

DECLARE @d TABLE ( d CHAR(1), p CHAR(2) )
INSERT  INTO @d
VALUES  ( 'A', 'P1' ),
        ( 'A', 'P2' ),
        ( 'B', 'P3' ),
        ( 'B', 'P4' ),
        ( 'B', 'P5' ),
        ( 'C', 'P6' ),
        ( 'C', 'P7' ),
        ( 'C', 'P8' ),
        ( 'C', 'P9' )

;WITH cte1 AS(SELECT *, 
                     1 i1, 
                     (id - 1) % 2 + 1 i2, 
                     (id - 1) % 3 + 1 i3, 
                     (id - 1) % 4 + 1 i4, 
                     (id - 1) % 5 + 1 i5 
              FROM @o),
cte2 AS(SELECT *, 
               ROW_NUMBER() OVER(PARTITION BY d ORDER BY p) rn, 
               COUNT(*) OVER(PARTITION BY d) cn FROM @d)

SELECT c2.d, c2.p, c1.id 
FROM cte1 c1
JOIN cte2 c2 ON c2.rn = CASE c2.cn WHEN 1 THEN c1.i1
                                   WHEN 2 THEN c1.i2
                                   WHEN 3 THEN c1.i3
                                   WHEN 4 THEN c1.i4
                                   WHEN 5 THEN c1.i5
                                   WHEN 6 THEN c1.id
                        END                        
ORDER BY        c2.d, c1.id