CODEWITHSUNDEEP
sqlsql-server
Mutex
Mutex

Reputation: 447

SQL Server : join all left table and repeat right

I have to join tables in a following way:

Table A:

1
2
3
4
5

Table B:

A
B

The result table should be:

1 A
2 B
3 A
4 B
5 A

Do you have any ideas how to do this?

Upvotes: 1

Views: 75

Answers (2)

areklipno
areklipno

Reputation: 528

Maybe something like this:

select A.nr, case when (A.nr%2=0) then b2.chr else b3.chr end letter
from A, B b2, B b3
where b2.chr = 'A' and b3.chr = 'B'

Upvotes: 1

dnoeth
dnoeth

Reputation: 60482

Assuming worst case, the column in table A is not a sequence without gaps and the number of rows in table B is not known in advance, you must apply a ROW_NUMBER on both tables and then join on a MODULO:

SELECT col1, col2
FROM
 (
   SELECT col1,
      ROW_NUMBER() OVER (ORDER BY col1) -1 AS rn
   FROM tableA
 ) AS A
JOIN 
 (
   SELECT col2,
      ROW_NUMBER() OVER (ORDER BY col2) -1 AS rn
   FROM tableB
 ) AS B
ON A.rn % (SELECT COUNT(*) FROM tableB) = B.rn

Upvotes: 1

Related Questions