How to get People Belonging to one city

USE AdventureWorks;
GO

SELECT   City = City + ', ' + StateProvinceCode,

         e.EmployeeID

INTO     #Location

FROM     HumanResources.Employee e

         INNER JOIN HumanResources.EmployeeAddress a

           ON e.EmployeeID = a.EmployeeID

         INNER JOIN Person.Address ad

           ON a.AddressID = ad.AddressID

         INNER JOIN Person.StateProvince sp

           ON sp.StateProvinceID = ad.StateProvinceID

ORDER BY City,

         EmployeeID;

SELECT TOP 5 * FROM #Location

/*

City              EmployeeID

Bellevue, WA      9

Bellevue, WA      21

Bellevue, WA      22

Bellevue, WA      23

Bellevue, WA      27

*/

SELECT DISTINCT City,

                Employees = convert(VARCHAR(2000),'|')

INTO   #City

FROM   #Location;

SELECT TOP 5 * FROM #City

/*

City              Employees

Bellevue, WA      |

Berlin, HH        |

Bordeaux, 33      |

Bothell, WA       |

Calgary, AB       |

*/

WHILE @@ROWCOUNT > 0

  BEGIN

    UPDATE c

    SET    c.Employees = c.Employees + convert(VARCHAR(3),l.EmployeeID) + '|'

    FROM   #City c

           INNER JOIN #Location l

             ON c.City = l.City

                AND charindex('|' + convert(VARCHAR(3),l.EmployeeID) + '|',

                              c.Employees,1) = 0

  END;  

To check out what happened we run a SELECT: 

SELECT TOP 7 * FROM #City ORDER BY City DESC 

/*  Partial results

City              Employees

Snohomish, WA     |10|32|36|45|92|134|138|187|230|246|

Seattle, WA       |7|18|25|29|31|43|51|52|53|56|57|64|73|83|85|86|...

San Francisco, CA |217|281|

Sammamish, WA     |50|71|78|84|93|113|124|125|133|201|211|219|…

Renton, WA        |11|12|34|77|121|140|145|195|198|218|225|238|241|259|…

Redmond, WA       |3|6|19|42|48|55|129|131|132|146|157|189|192|197|…

Portland, OR      |280|

*/