CODEWITHSUNDEEP
sqlsql-server
abodvdv
abodvdv

Reputation: 99

Cross Join with Filter?

i need to make Sp to distribute students to their sections the procedure take 2 string parameters StuID and SecID

in case I've send '1,2,3,4,5' as StuID and 'a,b' as SecID i'm using spliting function which well return tables 

Tb1 |   Tb2
1   |    a
2   |    b
3   |
4   |  
5   |

how can i get the following result

1 a
2 b
3 a
4 b
5 a
....

I've tried to do it via cross join but it did not show the result i want

 select US.vItem as UserID,SE.vItem as Section 
from split(@pUserID,',') us 
cross join split(@pSectionID,',') se

Upvotes: 0

Views: 461

Answers (1)

podiluska
podiluska

Reputation: 51494

Cross join isn't meant to work like that.

This will give you the results you want, but it's a bodge.

select t1.vItem, t2.VItem from
    ( select *, ROW_NUMBER() over (order by vItem) r from US ) t1
        inner join
    ( select *, ROW_NUMBER() over  (order by vItem desc) -1 r from SE ) t2
        on t2.r = t1.r % (select COUNT(*) from SE)
order by t1.vItem

Upvotes: 1

Related Questions