CODEWITHSUNDEEP
sqlsql-server
super-user
super-user

Reputation: 1057

SQL View Assign Alternating Data from Another Table

I cannot explain this thoroughly with words as English is not my native language so I will try visual presentation. First, I have a table that looks like this let's call it tblPerson:

tblPerson
ID   Name
1    John
2    Paul
3    James

Then I have another table tblPhones:

tblPhones
ID   Mobile
1    123456
2    654321

Now for my question, is it possible to create a view that will look like this:

Person-Phone
Name    Mobile
John    123456
Paul    654321
James   123456

What I want to display is a list of person, and use the tblPhones to assign the mobile column but assign it alternately. So if a new person is added lets say Mark. The view would look like this:

Person-Phone
Name    Mobile
John    123456
Paul    654321
James   123456
Mark    654321

How can I query this?

Upvotes: 1

Views: 41

Answers (2)

Liesel
Liesel

Reputation: 2979

@Giorgos beat me to it, but here's my version. You don't need the row_number window function assuming the IDs are contiguous (if they're not, you do :).

CREATE TABLE #tblPerson (ID INT,Name VARCHAR(5));
CREATE TABLE #tblPhones (ID INT, Mobile VARCHAR(6));

INSERT INTO #tblPerson(ID, Name) VALUES( 1, 'John'),( 2, 'Paul'),( 3, 'James');
INSERT INTO #tblPhones(ID, Mobile) VALUES( 1,'123456'),( 2,'654321');

SELECT
  Name, Mobile
FROM #tblPerson
JOIN #tblPhones ON #tblPhones.ID = ((#tblPerson.ID-1) % (SELECT COUNT(*) FROM #tblPhones) +1)
ORDER BY #tblPerson.ID

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Try this:

SELECT Name, Mobile
FROM (
  SELECT Name, 
         ROW_NUMBER() OVER (ORDER BY ID) As rn
  FROM tblPerson) AS t1
INNER JOIN (
  SELECT Mobile, 
         ROW_NUMBER() OVER (ORDER BY ID) AS rn,
         COUNT(*) OVER () AS cnt
  FROM tblPhones
) AS t2 ON (t1.rn - 1) % cnt + 1 = t2.rn

Demo here

Upvotes: 1

Related Questions