sql server 2005 - how to query to create a grouping of sets -

I have a common included table with two columns which are the key for other tables I for a range of records subscribers Are mapped.

  asset_map ----------- customer_id asset_id --------------- -------- 1 1 1 2 2 1 2 2 3 1 3 2 3 3 4 1 4 2  

There are about 10 properties, each client can be mapped to any of what I would like to achieve , Something like this:

  grouping customer_id asset_id -------------------------- ------ ---- 1 1 1 1 1 2 1 2 1 1 2 2 - - - - - - - - - - - - - - - - - - - 2 3 3 2 3 1 2 3 2 - - - - - - - - - - - - - - - - - - - - 1 4 1 1 4 2  

Note that customers 1,2 and 4 fall into the same group because they are property 1 And are mapped to 2. The customer is not in the group 3 as 1,2 and 3.

with rows (customer_id, asset_id) (select 1, 1 Union Select All 1, 2 Select All Union 2, Select 1 Union 2, 2 Select Union All 3, Select 1 Union All 3, 2 Union Select All 3, 3 All Select Union 4, 1 Union All Select Selection 4, 2) (Select cast (asset_id +) as Vargar (10). '[[([Text]] from rows as rays again. Customer_id = ro.customer_id XML path by ORDER ( Property) for property) The sector as RA. * From (select different customer_ id from rows) RO RO ro.customer_id

at RO RO RO ra.customer_id on RO