RSS

Combining Multiple Cell Values into Single Column

17 Jul

On occasion, there is a desire to concatenate column values from multiple rows to create a comma delimited list.
For example, from the following data:

he desired output is a list of Departments and Department employees, in the form of:

There are several methods to accomplish the desired output.

–Creating Table
CREATE TABLE Employees
(
EmployeeID INT,
EmployeeName VARCHAR(30),
DepartmentID INT
)

–Inserting Data
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (100, ‘Ahmed’, 1);
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (200, ‘Usama’, 1);
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (300, ‘Hana’, 1);
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (400, ‘Nagiub’, 1);
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (500, ‘Rahma’, 1);
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (600, ‘Mona’, 1);
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (700, ‘Abdul Rahman’, 2);
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (800, ‘Marwa’, 2);
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (900, ‘Layla’, 2);
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (1000, ‘Farieda’, 3);
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (1100, ‘Muhammad’, 3);
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (1200, ‘Ola’, 3);
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (1300, ‘Khaled’, 3);
INSERT INTO [Employees] ([EmployeeID], [EmployeeName], [DepartmentID]) VALUES (1400, ‘Mustafa’, 3);

–Select all data
SELECT * FROM [Employees]

–Combining data
SELECT [DepartmentID], (SELECT CAST([EmployeeName] AS VARCHAR(MAX)) + ‘,’ AS [text()]
FROM [Employees] AS [A]
WHERE A.DepartmentID = B.DepartmentID
ORDER BY [DepartmentID] FOR XML PATH ()) AS AllEmpoyees
FROM [Employees] AS [B]
GROUP BY [DepartmentID]

Reference: http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist.

 
Leave a comment

Posted by on July 17, 2012 in MS SQL Server

 

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

 
%d bloggers like this: