SQL - Combine rows into single row to retain parent record when duplicate exists

Question

I want to combine records with duplicate into single row. The surviving record will be updated with info from the duplicate if available. In the example below, I want to retain ID 500 then supply the missing data from its duplicate record which is ID 501. --->>>SQL Problem sample data image<<<---

CURRENT DATA:

ID  Group   Name         Identifier1    Identifier2 Birthday
500 1   Christopher Col   asdf            NULL        NULL
501 2   Christopher Col   asdf            qwerty    2/18/1987
502 1   Mickey            vbnx            tyui      1/25/1998
503 2   Minnie            ghjk            erty      4/23/2003

EXPECTED RESULT:

ID  Group   Name         Identifier1    Identifier2 Birthday
500 1   Christopher Col    asdf           qwerty    2/18/1987
502 1   Mickey             vbnx           tyui      1/25/1998
503 2   Minnie             ghjk           erty      4/23/2003

score 1 · Answer 1 · answered Nov 29 '17 at 09:20

1

This will need two steps if there is only one duplicate:

Update the primary record

UPDATE T1
SET T1.Group = ISNULL(T1.Group, T2.Group)
FROM Table T1
LEFT JOIN Table T2 ON T2.ID <> T1.ID AND T2.Name = T1.Identifier1 AND T2.Identifier1
WHERE ROW_NUMBER() OVER (PARTITION BY T1.Identifier1 ORDER BY ID) = 1

I pressume that 'Identifier1' is the unique way to identify the records - otherwise you need to change the query... also the updated fields need some fillup ;)

Delete all secondary data

DELETE FROM Table WHERE ROW_NUMBER() OVER (PARTITION BY T1.Identifier1 ORDER BY ID) > 1

This script will delete all records which are not the first when partitioney by the identifier.

PS: Will only work with T-SQL PPS: These are just dummy scripts and no guarantee they will work. But I hope they will give you the idea how to approach your goal.

answered Nov 29 '17 at 09:20

OlafW

700
6
22

Thanks sir Olaf. I will give this a try :) – SQL Knows Nov 29 '17 at 09:28
I have another question which in some reason I cannot post. The site is saying it's maybe a duplicate question, but it's not. – SQL Knows Nov 29 '17 at 10:22
I have a table with some columns having multiple values inside separated by comma. I want to split them as new rows. I can obtain correct result if there is only one column involved. But there are several columns with comma separated values to be split all at once. The problem is that the record is multiplying to unnecessary records when I use either OUTER APPLY dbo.split([columnName], ',') or cross apply STRING_SPLIT ([columnName], ',') in SQL. Can someone help me obtain the expected result using SQL or SSIS? [1]: https://i.stack.imgur.com/qgVwm.jpg – SQL Knows Nov 29 '17 at 10:24
@SQLKnows There are multiple answers for this question here. Did you checked https://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco yet? – OlafW Nov 29 '17 at 10:27
Or this mysql script could help you: https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows – OlafW Nov 29 '17 at 10:27
Thanks for this, but this queries work only for a single column with delimited values. I can also do this correctly. My problem is that I have four columns with comma separated values. And I need to split them and match the corresponding record as shown in my attached image https://i.stack.imgur.com/qgVwm.jpg – SQL Knows Nov 29 '17 at 10:36
Sorry, I'm out - this would take more time than I can spare right now. I think you will need to pimp the function to get 3 input strings and work on them separately for the output. At this moment it looks like you'll split them recursively and getting 2x2x2 records instead of just 2. By the way: Was my answer helpfull? If so please mark as answered. Thanks! – OlafW Nov 29 '17 at 10:57

score 0 · Answer 2 · answered Nov 29 '17 at 16:13

Please test yourself before implementing.

select  
    t1.id,
    t1.[Group],
    t1.Name,
    t1.Identifier1,
    isnull(t1.Identifier2,t2.Identifier2) Identifier2,
    Isnull(t1.Birthday,t2.Birthday) Birthday
from 
    #temp T1 
    left join #Temp t2 on t1.Name=t2.name and t1.[Group]<t2.[Group]
    Where t2.id is null

Also you you can find your answer for the question which you asked in comments. Answer to your question asked in comments

SQL - Combine rows into single row to retain parent record when duplicate exists

2 Answers2