Create comma-separated list


1- Create Table Emp

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATE TABLE [dbo].[Emp](

[Emp_Code] [int] NULL,

[Emp_Name] [nvarchar] (50)NULL)

ON [PRIMARY]

GO

2-  Insert Data

Insert into Emp values (1,‘Mostafa’),(1,‘Moahmed’),(2,‘Elsayed’),(2,‘Elmasry’)

GO

3- Create comma-separated list

WITH CTE AS (

SELECT DISTINCTEmp_Code FROM Emp )

SELECT Emp_Code, CommaList =STUFF((SELECT‘,’+ Emp_name FROM Emp

WHERE Emp_Code = CTE.Emp_Code ORDERBY Emp_name

FORXMLPATH(),TYPE).value(‘.’,‘varchar(max)’),1,1,)FROM CTE

ORDERBY Emp_Code;