Random allocation without duplicates

[Udaysekhar]

Hi,

We have a specific scenario as outlined below, any advise to implement without recursion.

Source data

ID       Empl

1          A

​1          B

​1          C

2          B

2          C

2          A

​3          A

3          C

​3          B

There is no limit on how many of the above duplicates can come from source.​Expected output is to randomly allocate ID to employee with out duplicating an ID for more than 1 employee & viceversaExpected o/p like below, but any combination is acceptable, w/o dups.

​ID    Empl

​1     C

​2     A

3     B

(Once an ID is allocated to employee C it should not be reused)

[ashok_paswan]

Hi Udaysekhar,

If you have same number of unique values for ID and EMP then below query should work. Please try and let me know...thanks !

SELECT A1.AID,B1.BEMP FROM

(

SELECT  AID, ROW_NUMBER() OVER (ORDER BY AID)  AS RWNUM FROM 

(SELECT ID AS AID FROM <TABLE>

QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID)=1) A

) A1

INNER JOIN

(SELECT BEMP, ROW_NUMBER() OVER (ORDER BY BEMP)  AS RWNUM FROM 

(SELECT EMP AS BEMP FROM <TABLE>

QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID)=1) B

)B1

ON A1.RWNUM = B1.RWNUM