# Cycle through values in a table

I am trying to find a solution for this problem, I have two tables

Table no 1

``````| no | ticket |
|----|--------|
| 1  | T1     |
| 2  | T2     |
| 3  | T3     |
``````

Table no 2

``````| id | CustomerId |
|----|------------|
| 1  | C1         |
| 2  | C2         |
``````

I require a below set

``````| Tickeid | CustomerId |
|---------|------------|
| T1      | C1         |
| T2      | C2         |
| T3      | C1         |
``````

It basically cycle with ticketid , I started with a cross join on both table but couldn't get the required result

EDIT:1 One change that can happen is the values in table 2 can have more than 2 values like in table 1 have T1,T2,T3,T4 and table 2 have C1,C2,C3 then I require T1C1,T2C2,T3C3,T4C1

Again

if table 2 is just C1

then I just Need T1C1.

Based on the recent changes to your question, I've completely rewritten my answer. There may be more efficient ways of doing this, but here's my solution. Let me know if I have misunderstood your request.

``````--set up demo data
declare @Table1 table (no int, ticket varchar(5))
insert into @Table1 (no, ticket) values
(1,'T1'),(2,'T2'),(3,'T3'),(4,'T4'),(5,'T5'),(6,'T6'),(7,'T7'),(8,'T8'),(9,'T9'),(10,'T10'),(11,'T11')

declare @Table2 table (ID int, CustomerId varchar(5))
insert into @Table2 (id, CustomerId) values(1,'C1'),(2,'C2'),(3,'C3'),(4,'C4')

--The actual solution
;
--Table2 row count
WITH Table2RowCount
AS (
SELECT count(*) AS T2RowCount
FROM @Table2
)
--Add row number (sequence) to Table1 rows
,Table1AndRowNumber
AS (
SELECT *
,row_number() OVER (
ORDER BY no
) AS sequence
FROM @Table1
)
--Add row number (sequence) to Table2 rows
,Table2AndRowNumber
AS (
SELECT *
,row_number() OVER (
ORDER BY id
) AS sequence
FROM @Table2
)
--Add a grouping column for Table1 using the modulus operator
--This grp column will be used to join up against the sequence column of Table2
,Table1Grouped
AS (
SELECT *
,CASE
WHEN t1arn.sequence % (t2rc.T2RowCount) = 0
THEN (t2rc.T2RowCount)
ELSE t1arn.sequence % (t2rc.T2RowCount)
END AS grp
FROM Table1AndRowNumber t1arn
CROSS APPLY Table2RowCount t2rc
)
SELECT t1g.ticket
,t2arn.CustomerId
FROM Table1Grouped t1g
JOIN Table2AndRowNumber t2arn ON t2arn.sequence = t1g.grp
``````

``````| ticket | CustomerId |
|--------|------------|
| T1     | C1         |
| T2     | C2         |
| T3     | C3         |
| T4     | C4         |
| T5     | C1         |
| T6     | C2         |
| T7     | C3         |
| T8     | C4         |
| T9     | C1         |
| T10    | C2         |
| T11    | C3         |
``````
• hoddgin, the solution is right as per the question but there is a slight change, table 2 can have more values so, if table 1 have t1,t2,t3,t4 and table 2 has c1,c2,c3 then I need t1c1,t2c2,t3c3,t4c1, the same way if table 1 has t1 and table 2 has c1,c2,c3 i just need t1,c1 Dec 21, 2017 at 9:35
• hoddgin, I am editing the question as per the change. Dec 21, 2017 at 9:36
• @Bijujose - based on the changes in your question, I’ve rewritten my answer Dec 22, 2017 at 9:22
• yup this is the one, I did some test for checking the results and its fine. Dec 23, 2017 at 5:39

Number arithmetic you need indeed (and the modulus operator).

The following takes care of gaps in the `id` and `no` values as well. The `ROW_NUMBER()` provides gapless numbers and the `-1` makes the numbers start from `0` (we need that because the `% n` operator gives results from `0` up to `n - 1`):

``````with t as
( select ticketid = ticket,
rn = row_number() over (order by no) - 1
from tickets
),
c as
( select customerid,
rn = row_number() over (order by id) - 1,
total_count = count(*) over ()
from customers
)
select t.ticketid,
c.customerid
from t join c
on t.rn % c.total_count = c.rn ;
``````