SQL Recursive Coalesce

Question

Hi all,

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.*, 
    (SELECT @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

Answer 1

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'

?

Answer 2

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]