RSS

Create comma-separated list

31 Oct

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;

 

 

 

 
Leave a comment

Posted by on October 31, 2012 in Script

 

Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s