I have a table with two id columns id1 and id2. Each row shows that 2 id values have been linked together. My actual data has both id columns as varchar(20) but I have used integers in the example below to illustrate the problem.
id1 id2 --- --- 1 2 1 3 3 4 3 5 6 7
I want to group together ids so that all linked ids are grouped together even if there is not a direct link between 2 id values.
id group_id -- ---- 1 1 2 1 3 1 4 1 5 1 6 2 7 2
I would like to achieve this in sql without using cursors if possible.
All advice gratefully received.
Update: I thought it would be useful to add a bit more background on the data this represents. The data represents records for businesses sourced from different data sources
The main table looks like this
id business_name postal_address business_classification x y group_id
The main table can contain duplicates / similar records which need to be associated. The table with id1 and id2 columns is a result of de-duplication using fuzzy string matching (levenshtein, word matching etc) and spatial proximity matching. I think that this table is actually an undirected graph containing many disconnected sub graphs e.g. group_id 1 and 2.I want to join all ids in the same network and update the group_id in the main table.
I think that the question is now how to identify all disconnected sub graphs on a graph using SQL server Find All Disconnected Sub Graphs - Java