COALESCE inside SQL Server Select statement

Question

I have a table "Tasks" that can have multiple users assigned to it, stored in table "Assignment".

I have q COALESCE query that groups my users into one string:

DECLARE @Names VARCHAR(8000)

SELECT @Names = COALESCE(@Names, '') + taskAssignment.FullName + '; '
FROM dbo.v_TaskAssignment taskAssignment
WHERE (taskAssignment.TaskId = @TaskId) 
  AND taskAssignment.AssignmentIsRemoved = 'False' 
  AND taskAssignment.FullName IS NOT NULL

SELECT @Names as Names

And my query for the tasks:

SELECT Id, Summary
FROM dbo.v_Task

I want my task query to have one more string column listing all my users delimited by a semi colon. However this does not seem to work:

DECLARE @Names VARCHAR(8000)

SELECT        
    dbo.v_Task.Id, dbo.v_Task.Summary, 
    (SELECT @Names = COALESCE(@Names, '') + taskAssignment.FullName + ';'
     FROM dbo.v_TaskAssignment taskAssignment
     WHERE (taskAssignment.TaskId = dbo.v_Task.Id) 
       AND taskAssignment.AssignmentIsRemoved = 'False' 
       AND taskAssignment.FullName IS NOT NULL
     SELECT @Names as Names) AS Assignements
FROM
    dbo.v_Task 
INNER JOIN
    dbo.v_TaskAssignment ON dbo.v_Task.Id = dbo.v_TaskAssignment.TaskId

I get the following error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.

How can I go about and get my Coalesce query inside my task select query?

Answer 1

If I understand what you're trying to do correctly, how about a somewhat simple method with XML PATH?

SELECT Id, Summary, STUFF(
  (SELECT ';' + FullName 
   FROM v_TaskAssignment ta
   WHERE t.Id = ta.TaskId
     AND ta.AssignmentIsRemoved = 'False' 
     AND ta.FullName IS NOT NULL
   FOR XML PATH ('')), 1, 1, '') Assignments
FROM dbo.v_Task t

An SQLfiddle to test with.