troubleshooting Question

SQL query to merge near dupes and preserve Ids

Avatar of Sailing_12
Sailing_12 asked on
Microsoft SQL Server* T-SQLSQL
5 Comments1 Solution133 ViewsLast Modified:
I need a query to merge near-identical rows in SQL, but retain the IDs of the removed rows in a delimited list in an additional column.

My source data looks something like this (let's assume all but the Id column is an exact match):

18047             1 3rd St      Suite A      Hometown      NJ      08000
1045136             1 3rd St              Suite A      Hometown      NJ      08000
2321             1 3rd St              Suite A      Hometown      NJ      08000
3311             1 3rd St              Suite A      Hometown      NJ      08000
3681             1 3rd St              Suite A      Hometown      NJ      08000
1750              1 Baker Rd      Ste C      Happy Hill      NJ      08111
1822             1 Baker Rd      Ste C      Happy Hill      NJ      08111
1935             1 Baker Rd      Ste C      Happy Hill      NJ      08111

The results I'm looking for are:
18047      1 3rd St                  Suite A      Hometown      NJ      08000   1045136|2321|3311|3681
1750      1 Baker Rd          Ste C              Happy Hill      NJ      08111   1822|1935

-- OR --

Including the persisting Id in the list would be even better:
18047      1 3rd St                  Suite A      Hometown      NJ      08000   18047|1045136|2321|3311|3681
1750      1 Baker Rd          Ste C              Happy Hill      NJ      08111   1750|1822|1935

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros