SQL-statement for two group-columns

Question

I have a problem creating a SQL-statement for sqlserver2008. I have the following data:

city     person     priority
-----------------------------------
Linz     Mike       1
Wien     Mike       1   
Linz     Tom        1
Wien     Tom        1
Linz     John       1
Linz     Sarah      2

This means that the persons Mike and Tom choose the cities Linz and Wien with priority 1.
John chooses Linz with priority 1.
Sarah chooses Linz with priority 2.

now I want the following output:

cities          persons          priority
-----------------------------------
Linz, Wien      Mike, Tom       1
Linz            John            1
Linz            Sarah           2

I already have following SQL-Statement but I do not get the expected result as this query would say that John also has an entry for Wien with priority 1.

SELECT
(SELECT 
  STUFF((SELECT ', ' + d.City 
  FROM (SELECT DISTINCT d2.City FROM dbo.DummyTable d2
        WHERE d2.Priority = d1.Priority) d
  FOR XML PATH('')), 1, 2, '')
  ) 
AS Cities,
(SELECT 
  STUFF((SELECT ', ' + d.Person 
  FROM (SELECT DISTINCT d2.Person FROM dbo.DummyTable d2
        WHERE d2.Priority = d1.Priority) d
  FOR XML PATH('')), 1, 2, '')
  ) 
AS Persons,
d1.Priority
FROM
dbo.DummyTable d1
GROUP BY d1.Priority

You can also use this SQL Fiddle

Any ideas how this query could be written in SQL?

Answer 1

Here's a way to do it:

;with PersonCityGroupPreferences as (
  select
    Person,
    Priority, 
    stuff ((
      select ', ' + d2.City
      from DummyTable d2
      where d1.Priority = d2.Priority
          and d1.Person = d2.Person
      FOR XML PATH('')
    ), 1, 2, '') Cities
  from DummyTable d1
  group by Person, Priority
)
select 
  Cities,
  stuff ((
    select ', ' + p2.Person
    from PersonCityGroupPreferences p2
    where p1.Cities = p2.Cities
      and p1.Priority = p2.Priority
    FOR XML PATH('')
  ), 1, 2, '') Persons,
  Priority
from PersonCityGroupPreferences p1
group by Priority, Cities

SQLFiddle link: http://www.sqlfiddle.com/#!3/d831d/57

In order to achieve the final result, I divided the solution into two steps:

1. Obtain a result set that groups the data by Person and Priority and contains the comma-separated list of cities as a third column

2. Take the result set obtained a point 1 and do the same thing, but now group by the columns Cities (the comma-separated list) and Priority and produce a comma-separated list of corresponding persons.

In the query above, step 1 is this query:

select
  Person,
  Priority, 
  stuff ((
    select ', ' + d2.City
    from DummyTable d2
    where d1.Priority = d2.Priority
    and d1.Person = d2.Person
    FOR XML PATH('')
  ), 1, 2, '') Cities
from DummyTable d1
group by Person, Priority

Here's how the partial results look in SQL: http://www.sqlfiddle.com/#!3/d831d/58

I then exposed the first query as a CTE, making it available to the (outer) query 2, which essentially does the same thing, but with a different grouping criteria.