convert rows to columns based on condition

Question

Hi, I am looking to convert the below table from rows to columns based some conditions, bascially, I am looking for duplicate pairs, not sure if it is possible.

So, if the emails are the same, they should be the parent and child records, if the licenceMatch is OK, and the value of Dob equals the value of DobMatch, it is the parent record, and the others are child records

Source table:

id Licence# LicenceMatch Email DoB DoBMatch DupeCount
122474 157741 OK 1970tariq@Stuff .com 9/3/1970 9/3/1970 2
109972 58118 NULL 1970tariq@Stuff .com 9/3/1970 NULL 2
125026 191936 OK 199inder@Stuff .com 2/23/1996 2/23/1996 2
125326 NULL NULL 199inder@Stuff .com 2/23/1996 NULL 2
106462 151289 OK 6173926@Stuff .com 2/23/1969 11/5/1975 2
116153 151789 OK 6173926@Stuff .com 2/23/1969 2/23/1969 2
122114 189591 OK 7837gkaur@Stuff .com 3/26/1983 3/26/1983 2
122113 NULL NULL 7837gkaur@Stuff .com 3/26/1983 NULL 2
126411 193294 OK zoheb97@Stuff .com 7/15/1997 7/15/1997 3
126409 NULL NULL zoheb97@Stuff .com 7/15/1997 NULL 3
126410 NULL NULL zoheb97@Stuff .com 7/15/1997 NULL 3

Target table:
Parent id Child id
122474 109972
125026 125326
116153 106462
122114 122113
126411 126409
126411 126410

Answer 1

Declare @Sample Table(id int, Licence# varchar(10), LicenceMatch char(2), Email varchar(30), DoB date, DoBMatch date, DupeCount int);  
Insert @Sample(id, Licence#, LicenceMatch, Email, DoB, DoBMatch, DupeCount) Values  
(122474, 157741, 'OK', '1970tariq@gmail.com', '9/3/1970',  '9/3/1970', 2),  
(109972, 58118, NULL,  '1970tariq@gmail.com', '9/3/1970',  NULL, 2),  
(125026, 191936, 'OK', '199inder@gmail.com',  '2/23/1996', '2/23/1996', 2),  
(125326, NULL, NULL,   '199inder@gmail.com',  '2/23/1996', NULL, 2),  
(106462, 151289, 'OK', '6173926@gmail.com',   '2/23/1969', '11/5/1975', 2),  
(116153, 151789, 'OK', '6173926@gmail.com',   '2/23/1969', '2/23/1969', 2),  
(122114, 189591, 'OK', '7837gkaur@gmail.com', '3/26/1983', '3/26/1983', 2),  
(122113, NULL, NULL,   '7837gkaur@gmail.com', '3/26/1983', NULL, 2),  
(126411, 193294, 'OK', 'zoheb97@gmail.com',   '7/15/1997', '7/15/1997', 3),  
(126409, NULL, NULL,   'zoheb97@gmail.com',   '7/15/1997', NULL, 3),  
(126410, NULL, NULL,   'zoheb97@gmail.com',   '7/15/1997', NULL, 3);  
  
;With cte As  
(Select s.id, s.Email  
From @Sample s  
Where s.DOB = s.DOBMatch And s.LicenceMatch = 'OK')  
Select c.id As Parent, s.id As Child  
From cte c  
Inner Join @Sample s On c.Email = s.Email  
Where c.id <> s.id  
Order By Parent, Child;  

Tom

Answer 2

Try a query:

select t1.id as [Parent id], t2.id as [Child id]  
from SourceTable t1  
left join SourceTable t2 on t2.Email = t1.Email and t2.id != t1.id  
where t1.LicenceMatch = 'OK' and t1.DoB = t1.DoBMatch  

Answer 3

Hi @sdnd2000
Both Tom and Viorel's answer work for you. You could also check this query using Tom's sample data:

;WITH CTE AS  
(  
 SELECT id,Email,CASE WHEN LicenceMatch='OK' AND DoB=DoBMatch THEN 0 ELSE 1 END AS TAG  
 FROM @Sample  
)  
SELECT C1.id AS Parent_ID,C2.id AS Child_ID  
FROM CTE C1 JOIN CTE C2 ON C1.Email=C2.Email AND C1.TAG+1=C2.TAG  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.