StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCOALESCE inside SQL Server Select statement
text
Body
copied!<p>I have a table "Tasks" that can have multiple users assigned to it, stored in table "Assignment".</p> <p>I have q COALESCE query that groups my users into one string:</p> <pre><code>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 </code></pre> <p>And my query for the tasks:</p> <pre><code>SELECT Id, Summary FROM dbo.v_Task </code></pre> <p>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:</p> <pre><code>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 </code></pre> <p>I get the following error:</p> <blockquote> <p>Msg 102, Level 15, State 1, Line 3<br> Incorrect syntax near '='.<br> Msg 102, Level 15, State 1, Line 8<br> Incorrect syntax near ')'.</p> </blockquote> <p>How can I go about and get my Coalesce query inside my task select query?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload