2

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.

0

2 Answers 2

2

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         |
4
  • 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
    – Biju jose
    Dec 21, 2017 at 9:35
  • hoddgin, I am editing the question as per the change.
    – Biju jose
    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.
    – Biju jose
    Dec 23, 2017 at 5:39
2

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.