0

This is a part of my previous question.

How can I achieve the following for all the records in my table using a single query?

SET @TeamMembers = TeamMembers from tblProjects where ID = '125'

DECLARE @MemberEmails TABLE(col VARCHAR(255))
DECLARE @MemberNames TABLE(col VARCHAR(255))
DECLARE @Names VARCHAR(255)

INSERT INTO @MemberEmails
SELECT * FROM [dbo].[fnSplitString] (@TeamMembers, ',')

INSERT INTO @MemberNames
SELECT u.NAME FROM @MemberEmails e LEFT OUTER JOIN Users u ON e.col = u.EMAILID

SELECT @Names =
   STUFF((SELECT '; ' + RTRIM(CONVERT(CHAR(50),col))
   FROM   @MemberNames b        
   FOR XML PATH('')),1,1,'') 

UPDATE tblProjects SET Team = @Names  WHERE ID = '125'
0

I was able to achieve the update of all records my table, using the below set of commands :

DECLARE @MemberNames TABLE(ID VARCHAR(50), Person VARCHAR(255))
INSERT INTO @MemberNames 
SELECT P.ID, u.NAME
FROM dbo.tblProjects AS P 
CROSS apply dbo.fnSplitString(P.TeamMembers, ',') AS S      
     LEFT OUTER JOIN Users u ON s.splitdata = u.EMAILID     

DECLARE @Names TABLE(ID VARCHAR(50), Name VARCHAR(MAX) ) 
INSERT INTO @Names
SELECT t.ID, STUFF(ISNULL((SELECT '; ' + x.Person
            FROM @MemberNames x
           WHERE x.ID = t.ID
        GROUP BY x.Person
         FOR XML PATH (''), TYPE).VALUE('.','VARCHAR(max)'), ''), 1, 2, '') 
    FROM @MemberNames t
    GROUP BY t.ID 
UPDATE tblProjects SET Team = Name FROM tblProjects pr INNER JOIN @Names ON ID = pr.ID

I have a table-valued function fnSplitString for splitting a string based on a delimiter.

p.s. I couldn't get the updates using a single query, as I had mentioned in my question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.