Avatar of Sailing_12
Sailing_12

asked on 

SQL query to merge near dupes and preserve Ids

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


Thanks!
Microsoft SQL Server* T-SQLSQL

Avatar of undefined
Last Comment
Sailing_12

8/22/2022 - Mon