How to Create a Comma-Delimited List of Employee Names in SQL Server?

Query to create comma separated list in SQL Server

The query you provided is designed to create a comma separated list of employee names from a table. However, the complexity of the query may confuse you. Let's break it down step by step.

Step 1: Combine subqueries of employee names

Subquery:

SELECT E2.ename AS 'data()' 
FROM emp AS e2 
WHERE e1.deptno = e2.DEPTNO 
FOR xml PATH('')

Return the connected employee name as XML. The "data()" alias is used to force an error, which is then hidden by the FOR XML PATH('') clause. This will result in XML without any tags.

Step 2: Remove spaces

The

Replace() function is used to replace spaces in XML strings with commas:

allemp = Replace ((SELECT E2.ename AS 'data()' 
                       FROM emp AS e2 
                       WHERE e1.deptno = e2.DEPTNO 
                       FOR xml PATH('')), ' ', ', ') 

Step 3: Grouping and Output

Use GROUP BY DEPTNO to group queries by department number. Then, display the output as a comma-separated list of employee names for each department.

Alternative query

The following query can also be used to achieve the same results, with some improvements:

SELECT  E1.deptno, 
        STUFF(( SELECT  ', ' + E2.ename 
                FROM    emp AS e2 
                WHERE   e1.deptno = e2.DEPTNO 
                FOR XML PATH('')
            ), 1, 2, '') 
FROM    EMP AS e1 
GROUP BY DEPTNO; 

This query uses the STUFF() function to remove the first comma and space. It also omits the alias in the subquery to avoid creating XML tags.

The above is the detailed content of How to Create a Comma-Delimited List of Employee Names in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!