How to Generate Comma-Delimited Lists of Employee Names by Department in SQL Server?

Generating Comma-Separated Employee Lists per Department in SQL Server

This SQL Server query efficiently creates comma-separated lists of employee names, organized by department, from the EMP table. The process cleverly uses subqueries and string manipulation techniques.

A subquery, embedded within the FOR XML PATH('') clause, gathers employee names for each department. This concatenation happens without XML tags, producing an XML fragment containing all employee names for a given department. This fragment is stored in the allemp variable.

Next, the REPLACE function replaces spaces within the allemp string with commas, transforming it into the desired comma-separated list. The final result, grouped by deptno, provides a single row per department, displaying the comma-separated employee names.

Here's the output:

<code>deptno  allemp
10      CLARK,KING,MILLER
20      SMITH,JONES,SCOTT,ADAMS,FORD
30      ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES</code>

The FOR XML PATH('') clause, without an explicit alias, generates the XML fragment without enclosing tags. For more robust handling of special XML characters, consider adding the TYPE keyword (as shown in the appendix), ensuring proper escaping.

This method offers a practical solution for generating comma-separated lists, useful for data export or report generation.

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