June 22, 2014

SQL Server - One to one record mapping between two table without any common column

Today I had to write a script that maps one to one records without any common columns between two tables. I think it would be helpful for you as well.

declare @Customer table (CustomerId int, CustomerName varchar(50))
declare @Branch table (BranchId int, BranchName varchar(50))

insert into @Customer values (1, 'Kale')
insert into @Customer values (2, 'Gore')
insert into @Customer values (3, 'Sailo')
insert into @Customer values (4, 'Mailo')

insert into @Branch values (1, 'Dharan Branch')
insert into @Branch values (2, 'Biratnagar Branch')

;with Customer (RowNumber, CustomerId, CustomerName) as
(
    select row_number() over(order by CustomerID) as RowNumber, * from @Customer
)
,Branch (RowNumber, BranchId, BranchName) as
(
    select row_number() over(order by BranchId) as RowNumber, * from @Branch
)
select cust.CustomerId, cust.CustomerName, bra.BranchId, bra.BranchName
from Customer as cust
left join Branch as bra
on cust.RowNumber = bra.RowNumber

No comments: