CODEWITHSUNDEEP
sqlsql-servercoalesce
LanFeusT
LanFeusT

Reputation: 2432

COALESCE inside SQL Server Select statement

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?

Upvotes: 3

Views: 7981

Answers (2)

Johnv2020
Johnv2020

Reputation: 2146

A CTE would work here - something like this maybe 

DECLARE @Names VARCHAR(8000)

;WITH Naming (Id, Names, Name) AS ( SELECT t.Id ,COALESCE(@Names, '') + ta.FullName + ';' ,Name FROM dbo.v_taskAssignment tA JOIN dbo.v_Task t ON ta.TaskId = t.Id )

SELECT T.Id, T.Summary, Names, Name FROM Naming N JOIN v_Task T ON N.Id = T.Id

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

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.

Upvotes: 2

Related Questions