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!