create table #tbl1(cola nvarchar(max)) insert into #tbl1...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

create table #tbl1(cola nvarchar(max))
insert into  #tbl1 values
('1'),('2'),('3'),('4')
 
 
 
create table #tbl2(colb nvarchar(max))
insert into  #tbl2 values
('a'),('b'),('c'),('d')


 
 
 --working :
 ------------
 /*SELECT table1.cola, table2.colb FROM
(SELECT cola, ROW_NUMBER() OVER (ORDER BY cola) AS rn1 FROM #tbl1) table1,
(SELECT colb, ROW_NUMBER() OVER (ORDER BY colb) AS rn2 FROM #tbl2) table2
WHERE table1.rn1 = table2.rn2*/

SELECT cola, colb
FROM (
  SELECT a.cola, b.colb,
         rna=row_number() over (partition by colb
                                order by cola),
         rnb=row_number() over (partition by cola
                                order by colb)
  FROM #tbl1 a
  CROSS JOIN #tbl2 b
  ) X
WHERE rna=rnb;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results