How to Concatenate Multiple Rows within a Single Row in SQL Server

SQL Server STUFF ()  function and  FOR XML PATH () clause to Concatenate multiple Rows in a single Row
In SQL Server, we can concatenate Multiple rows within a single row as a Text using the STUFF ()  function and For XML PATH () clause as follows : 

Select DISTINCT [Dept_Id],[Dept_Name],SUM(NetSales)[NetSales],
STUFF(
(SELECT ';'+[Emp_Name] AS [text()] from [Sales_Dtls] T2
WHERE T1.Dept_Id=T2.Dept_Id
FOR XML PATH(''))
,1,1,'' ) [Sales_Team]
FROM [Sales_Dtls] T1 Group by [Dept_Id],Dept_Name

Example :
Suppose we have a Sales Table data as follows :
USE [TAMATAM]
GO

SELECT [Emp_Id]
      ,[Emp_Name]
      ,[Gender]
      ,[Job_Id]
      ,[Dept_Id]
      ,[Dept_Name]
  [NetSales]
  FROM [dbo].[Sales_Dtls]

GO

From the above Sales table if you want to see the Concatenated list of all the Sales people by Department, who generated that Sales, we do it as follows :

SELECT    DISTINCT  [Dept_Id],[Dept_Name],SUM(NetSales)[NetSales],
            STUFF((    SELECT ',' + TBL_Sub.[Emp_Name]
                             FROM [Sales_Dtls] TBL_Sub
                        WHERE
TBL_Sub.[Dept_Id] = Tbl_Main.[Dept_Id]
                        FOR XML PATH('')), 1, 1, '' )
                             AS [Sales_Team]
     FROM  [Sales_Dtls] Tbl_Main Group by [Dept_Id],Dept_Name

Output :
The concatenated list of Sales Team in a Single row separated by semicolon(;) as follows :

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------