question

MidhunJose-4709 avatar image
0 Votes"
MidhunJose-4709 asked EchoLiu-msft edited

Please solve this question

There is a Customer table that has a list of customers and Gift Voucher table that has list of gift vouchers. No of vouchers is less than No of customers. Customer table has one column Customer_Id. Voucher has one column Voucher_id.

Customer_Id
Abhinash
Vipin
Mahesh
Bijoy
Bhabani
Ashutosh


Voucher_Id
ABXFH
SDFGH
ERTYY
PPLKM

Develop a SQL query that will assign one voucher to one customer until all vouchers are exhausted and populate the table as shown.
Customer_Key Gift Voucher Key
Abhinash ABXFH
Vipin SDFGH
Mahesh ERTYY
Bijoy PPLKM
Bhabani -
Ashutosh -

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Please try:

 CREATE TABLE Customer (Customer_Id varchar(255));
 INSERT INTO Customer VALUES('Customer_Id'),('Abhinash'),('Vipin'),('Mahesh'),('Bijoy'),('Bhabani'),('Ashutosh')
    
 CREATE TABLE Voucher (Voucher_Id varchar(255) UNIQUE);
 INSERT INTO Voucher VALUES('ABXFH'),('SDFGH'),('ERTYY'),('PPLKM')
    
 ;with cte 
 as(select *,row_number() over(order by Customer_Id) rr from Customer)
 ,cte2 as(select *,row_number() over(order by Voucher_Id) rr from Voucher)
    
 select Customer_Id Customer_Key,Voucher_Id Gift_Voucher_Key
 from cte c1
 join cte2 c2 on c1.rr=c2.rr

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.


Best Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.