What this query does to create comma delimited lis

Question

I've written this query with the help of google to create a delimited list from a table but I didn't understand anything from this query.

Can anyone explain me what's happening

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

Gives me result

10  CLARK, KING, MILLER
20  SMITH, JONES, SCOTT, ADAMS, FORD
30  ALLEN, WARD, MARTIN, BLAKE, TURNER, JAMES

Answer 1

The simplest way of explaining it is to look at how FOR XML PATH works for actual XML. Imagine a simple table Employee:

EmployeeID      Name
1               John Smith
2               Jane Doe

You could use

SELECT  EmployeeID, Name
FROM    emp.Employee
FOR XML PATH ('Employee')

This would create XML as follows

<Employee>
    <EmployeeID>1</EmployeeID>
    <Name>John Smith</Name>
</Employee>
<Employee>
    <EmployeeID>2</EmployeeID>
    <Name>Jane Doe</Name>
</Employee>

Removing the 'Employee' from PATH removes the outer xml tags so this query:

SELECT  Name
FROM    Employee
FOR XML PATH ('')

Would create

    <Name>John Smith</Name>
    <Name>Jane Doe</Name>

What you are then doing is not ideal, the column name 'data()' forces an sql error because it is trying to create an xml tag which is not a legal tag, so the following error is generated:

Column name 'Data()' contains an invalid XML identifier as required by FOR XML; '('(0x0028) is the first character at fault.

The correlated subquery hides this error and just generates the XML with no tags:

SELECT  Name AS [Data()]
FROM    Employee
FOR XML PATH ('')

creates

John Smith Jane Doe

You are then replacing spaces with commas, fairly self explanatory...

If I were you I would adapt the query slightly:

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; 

Having no column alias will mean no xml tags are created, and adding the comma within the select query means any names with spaces in will not cause errors,STUFF will remove the first comma and space.

ADDENDUM

To elaborate on what KM has said in a comment, as this seems to be getting a few more views, the correct way to escape XML characters would be to use .value as follows:

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

Answer 2

SQL Server 2017 makes this much easier with the new STRING_AGG. Recently came across this post and switched my STUFF/FOR XML strategy to use the new string function. Also avoids the need to do an extra JOIN/SUBQUERYand the overhead of FOR XML (and the odd-encoding issues) and hard to interpret SQL.

SELECT  E1.deptno, 
        STRING_AGG(E1.ename, ', ') AS allemp
FROM    EMP AS e1 
GROUP BY DEPTNO; 

Note: Also be sure to check out the counterpart STRING_SPLIT to make working with SQL delimited data much easier.

Answer 3

The outer query retrieves a list of department numbers, and the subquery is then run for each department number to return all names belonging to that department. The subquery uses the FOR XML statement to format the output into a single line comma separated list.

Answer 4

Take it apart step by step - from the inside out.

Step 1:

Run the innermost query and see what it produces:

SELECT E2.ename AS 'data()' 
FROM emp AS e2 
WHERE e2.DEPTNO = 10
FOR XML PATH('')

You should get an output something like:

CLARK KING MILLER

Step 2:

The REPLACE just replaces spaces with , - thus turning your output into

CLARK, KING, MILLER

Step 3:

The outer query gets the deptno value - plus the results from the inner query - and produces your final result.