How to execute statement for each row and return entire result

Question

This is a continuation of a previous question: Find groups with matching rows

I have a table which contains people and the cars that they own

+-------+-------+
| Name  | Model |
+-------+-------+
| Bob   | Camry |
| Bob   | Civic |
| Bob   | Prius |
| John  | Camry |
| John  | Civic |
| John  | Prius |
| Kevin | Civic |
| Kevin | Focus |
| Mark  | Civic |
| Lisa  | Focus |
| Lisa  | Civic |
+-------+-------+

This query gives me everyone who has the exact same cars as Lisa, as well as Lisa herself, which is fine.

;with cte as (
  select *
    , cnt = count(*) over (partition by name)
  from t
)
, matches as (
  select x2.name
  from cte as x 
    inner join cte as x2
       on x.model = x2.model
      and x.cnt   = x2.cnt 
      and x.name  = 'Lisa'
  group by x2.name, x.cnt
  having count(*) = x.cnt
)
select t.* 
from t
  inner join matches m
    on t.name = m.name

Result:

+-------+-------+
| name  | model |
+-------+-------+
| Lisa  | Civic |
| Lisa  | Focus |
| Kevin | Civic |
| Kevin | Focus |
+-------+-------+

If i wanted to find all people who owns the same cars as Bob, i rerun the query and the result should give me John.

Right now, i have a list of names in Java, and for each name, i run this query. It is really slow. Is there anyway to find ALL people who own the same cars and partition the results into groups within a single database call?

For example, using the first table. I could run a query that would group the names. Notice how Mark has disappeared, because he does not own the exact same cars as someone else, only a subset.

+-------+-------+-------+
| Name  | Model | Group |
+-------+-------+-------+
| Bob   | Camry |     1 |
| Bob   | Civic |     1 |
| Bob   | Prius |     1 |
| John  | Camry |     1 |
| John  | Civic |     1 |
| John  | Prius |     1 |
| Kevin | Civic |     2 |
| Kevin | Focus |     2 |
| Lisa  | Focus |     2 |
| Lisa  | Civic |     2 |
+-------+-------+-------+

This result set is also fine, i just need to know who belongs in what group, i can fetch their cars later.

+-------+-------+
| Name  | Group |
+-------+-------+
| Bob   |     1 |
| John  |     1 |
| Kevin |     2 |
| Lisa  |     2 |
+-------+-------+

I need to somehow loop over a list of names and find all people who own the same cars, and then combine it all into a single result set.

Answer 1

You can do this two ways. One way is to do the complex joins. The other way is a short-cut. Just aggregate the cars into a string and compare the strings.

with nc as (
      select n.name,
             stuff( (select ',' + t.model
                     from t
                     where t.name = n.name
                     order by t.model
                     for xml path ('')
                    ), 1, 1, '') as cars
      from (select distinct name from t) n
     )
select nc.name, nc.cars, dense_rank() over (order by nc.cars)
from nc
order by nc.cars;

This creates a list with the names and the list of cars as a comma delimited list. If you like you can join back to the original table to get the original rows.

Answer 2

Using the concatenation method like vkp's answer on the previous question would work here as well if we add dense_rank():

with cte as (
select 
    name
  , models = stuff((
      select 
        ',' + i.model
      from t i 
      where i.name=t.name
      order by 1
      for xml path(''), type).value('.','varchar(max)')
      ,1,1,'') 
from t
group by name
)
select 
    name
  , models
  , dr = dense_rank() over (order by models)
from cte

rextester: http://rextester.com/GTT11495

results:

+-------+-------------------+----+
| name  |      models       | dr |
+-------+-------------------+----+
| Bob   | Camry,Civic,Prius |  1 |
| Mark  | Civic             |  2 |
| Kevin | Civic,Focus       |  3 |
| Lisa  | Civic,Focus       |  3 |
+-------+-------------------+----+