SQL Recursive Coalesce

I'm trying to create a column that contains all cities of the referenced addresses.

DECLARE @AddressList nvarchar(max)
SELECT @AddressList = COALESCE(@AddressList + ' ', '') + City  FROM [Address]

SELECT
    Employee.*, 
    (SELEC开发者_Go百科T @AddressList) AS AddressCities
FROM Employee

But I dont know where to put the WHERE clause.

...
    (SELECT @AddressList WHERE EmployeeId = Employee.EmployeeId) AS AddressCities
...

The above test doesnt work..

Table schemas are:

Employee

EmployeeId

Name

Address

Street

City

EmployeeId

If i understand you correctly, you wish to show all Cities in a single column for the employee. So you wish to GROUP BY and CONCAT.

Using Sql Server 2005, try this (working example)

DECLARE @Employee TABLE(
        EmployeeId INT,
        NAME VARCHAR(100)
)

INSERT INTO @Employee (EmployeeId,[NAME]) SELECT 1, 'A'
INSERT INTO @Employee (EmployeeId,[NAME]) SELECT 2, 'B'

DECLARE @Address TABLE(
        Street VARCHAR(50),
        City VARCHAR(50),
        EmployeeId INT
)

INSERT INTO @Address (Street,City, EmployeeId) SELECT 'A','A', 1
INSERT INTO @Address (Street,City, EmployeeId) SELECT 'B','B', 1
INSERT INTO @Address (Street,City, EmployeeId) SELECT 'C','C', 1
INSERT INTO @Address (Street,City, EmployeeId) SELECT 'D','D', 2
INSERT INTO @Address (Street,City, EmployeeId) SELECT 'E','E', 2
INSERT INTO @Address (Street,City, EmployeeId) SELECT 'F','F', 2


SELECT  e.EmployeeId,
        e.[NAME],
        (
            SELECT  al.City + ','
            FROM    @Address al
            WHERE   al.EmployeeId = e.EmployeeId
            FOR XML PATH('')
        )
FROM    @Employee e
GROUP BY e.EmployeeId,
        e.[NAME]

Do need more information about what you mean by 'column that contains all cities'. How is what you want different to the following might help you phrase the question

SELECT e.EmployeeId,e.Name,a.City
FROM Employee e
INNER JOIN Address a ON a.EmployeeId = e.EmployeeId
GROUP BY e.EmployeeId,e.Name

-- update

I think I see what you mean, do you want like:

EmployeeID | Name | Address
1          | John | 'London','Paris','Rome'
2          | Jane | 'New York','Miami'

?