declare @FirstTable table (StudentId int, SubjectId int)
declare @SecondTable table (MarksId int, RankId int, LastRank int)

insert into @FirstTable values (1, 1)
insert into @FirstTable values (1, 2)
insert into @FirstTable values (1, 3)

insert into @SecondTable values (1, 4, 10)
insert into @SecondTable values (1, 5, 11)

;with XmlTable (RowNumber, StudentId, SubjectId) as
(
    select row_number() over(order by StudentId) as RowNumber, * from @FirstTable
)
,TechnicalIdsTable (RowNumber, MarksId, RankId, LastRank) as
(
    select row_number() over(order by MarksId) as RowNumber, * from @SecondTable
)
select x.StudentId, x.SubjectId, t.MarksId, t.RankId, t.LastRank from XmlTable x
left join TechnicalIdsTable t
on x.RowNumber = t.RowNumber

declare @Students table (StudentId int, StudentName varchar(50), EnrolledOn datetime)
declare @Sports table (SportId int, SportName varchar(50))

insert into @Students values (1, 'John', '2013-05-25 10:00:00')
insert into @Students values (2, 'Jerry', '2013-05-25 14:30:00')
insert into @Students values (3, 'Jane', '2013-05-26 10:30:00')

insert into @Sports values (1, 'Golf')
insert into @Sports values (2, 'Foot ball')

;with Students (RowNumber, StudentId, StudentName, EnrolledOn) as
(
    select row_number() over(order by EnrolledOn) as RowNumber, * from @Students
)
,Sports (RowNumber, SportId, SportName) as
(
    select row_number() over(order by SportId) as RowNumber, * from @Sports
)
select s1.StudentId, s1.StudentName, s1.EnrolledOn, s2.SportId, s2.SportName from Students s1
left join Sports s2
on s1.RowNumber = s2.RowNumber