SQL server query to search and stuff multiple rows

Question

I have table employee_table which is like this

org     employeeid (int)    firstname   lastname
1234    56788934            Suresh      Raina
1234    56793904            Virat       Kohli

then i have project_table which is like this

Project     members (varchar)
A123        56788934,56793900

Now i need to fetch corresponding names of employees and stuff in a single row like this.

Project     members (varchar)
A123        Suresh Raina, Virat Kohli

I have written below query which is not working. please help.

SELECT project,
   (
            SELECT   message_text = Stuff(
                     (
                            SELECT ', ' + Concat(firstname,' ',lastname)
                            FROM   employee_table t1
                            WHERE  t1.org = t2.org
                            AND    CONVERT(VARCHAR,t1.userid) IN (Concat('''',Replace(pt.members,',',''','''),'''')) --adding single quotes at start and end of each number
                                   FOR xml path ('')) , 1, 1, '')
            FROM     employee_table t2
            WHERE    t2.userid IN
            group BY org;) FROM project_table pt 

Answer 1

Here is a solution for SQL Server 2017 onwards.

It is using a pair of handy functions available in SQL Server 2017:

• STRING_SPLIT()
• STRING_AGG()

Method #2 covers solution for SQL Server 2008 onwards.

SQL

-- DDL and sample data population, start
DECLARE @employee TABLE (org INT, employeeid INT, firstname VARCHAR(20), lastname VARCHAR(30));
INSERT INTO @employee (org, employeeid, firstname, lastname) VALUES
(1234, 56788934, 'Suresh', 'Raina'),
(1234, 56793904, 'Virat', 'Kohli');

DECLARE @project TABLE (project CHAR(4), members VARCHAR(MAX));
INSERT INTO @project (project, members) VALUES
('A123', '56788934,56793904');
-- DDL and sample data population, end

-- Method #1, SQL Server 2017 onwards
SELECT p.project, STRING_AGG(CONCAT(e.firstname, SPACE(1), e.lastname), ', ') AS members
FROM @project AS p CROSS APPLY STRING_SPLIT(members, ',') AS s
    INNER JOIN @employee AS e ON s.value = e.employeeid
GROUP BY p.project;

-- Method #2, SQL Server 2008 onwards
DECLARE @separator CHAR(1) = ',';

;WITH rs AS
(
    SELECT *
      , TRY_CAST('<root><r><![CDATA[' + 
         REPLACE(members, @separator, ']]></r><r><![CDATA[') + ']]></r></root>' AS XML) AS xmldata
    FROM @project
), cte AS 
(
    SELECT project
        , e.*
    FROM rs CROSS APPLY xmldata.nodes('/root/r/text()') AS t(c)
        INNER JOIN @employee AS e ON c.value('.','INT') = e.employeeid
)
SELECT project
    , STUFF((SELECT @separator + CAST(CONCAT(o.firstname, SPACE(1), o.lastname) AS VARCHAR(30)) AS [text()]
         FROM cte AS O
         WHERE O.project = C.project 
         FOR XML PATH('')), 1, 1, NULL) AS Members
FROM cte AS c
GROUP BY project;

Output

+---------+---------------------------+
| project |          members          |
+---------+---------------------------+
| A123    | Suresh Raina, Virat Kohli |
+---------+---------------------------+