Concatenating Row Values using Transact-SQL

Sometime SQL programmers come across a requirement to concatene a result-set using T-SQL query.

Below is an example of this scenarion:

SELECT Dept_Code,Emp_Name

FROM Employee (NOLOCK)

ORDER BY Dept_Code,Emp_Name

And user wants to see the data in below format:

You can do these using Transact SQL. Below is the T-SQL query to achieve this solution:

Method1: Concatenation that uses the FOR XML clause with PATH mode:

SELECT E1.Dept_Code,

(

   SELECT Emp_Name + ',' FROM Employee E2

   WHERE E2.Dept_Code = E1.Dept_Code

   ORDER BY Emp_Name

   FOR XML PATH('')

) AS Emp_Name

FROM Employee E1

GROUP BY Dept_Code

Method2: There is a similar approach using the CROSS APPLY operator.

SELECT DISTINCT E1.Dept_Code, Temp.Emp_Name

FROM Employee E1 CROSS APPLY

(

   SELECT Emp_Name + ',' FROM Employee E2

   WHERE E2.Dept_Code = E1.Dept_Code

   ORDER BY Emp_Name

   FOR XML PATH('')

) AS Temp (Emp_Name)