Concatenate rows values as a comma separated string using FOR XML PATH and STUFF in SQL SERVER

Introduction: In this article I am going to share how to use FOR XML PATH() and STUFF in SQL SERVER to convert or concatenate multiple row values of a single column in a table as a comma delimited string in single query.

In previous articles i explained how to Convert or split comma separated string into table rows in sql server and Join first, middle and last name in sql server and Count number of occurrences of character or word in a string in sql and Remove first or last character from string or column in sql server and Insert multiple records in table in single insert statement

Implementation: Let’s create some temporary tables and queries for demonstration purpose.

Create a temporary table to store employee data and insert some dummy data in this table for demonstration purpose.

CREATE TABLE #tbEmployee 

(

  EmployeeId INT IDENTITY(1,1) PRIMARY KEY , 

  EmployeeName VARCHAR(50)

)

INSERT INTO #tbEmployee (EmployeeName) 

VALUES

('AMAN'), 

('RAMAN'),

('ANUJ'),

('KABEER'),

('AAMIR')

 Check inserted data

SELECT * FROM #tbEmployee

Query Result: 

EmployeeId	EmployeeName
1	AMAN
2	RAMAN
3	ANUJ
4	KABEER
5	AAMIR

Now suppose we want to display all the Employee names as a comma separated string then we have to write the following query:

Query to get all employees as a comma separated string using FOR XML PATH and STUFF.

SELECT STUFF((SELECT DISTINCT ', ' + CAST(EmployeeName AS VARCHAR(100)) FROM #tbEmployee FOR XML PATH('')),1,2,'') AS Employees

Note: here we are using FOR XML PATH to concatenate row values as comma delimited string and STUFF command to take the first character, in our case a comma, and replace it with nothing

So the query result will be as: 

Employees

AAMIR, AMAN, ANUJ, KABEER, RAMAN

  

Now let’s make the example more useful. Suppose it is required to display all employees with their qualifications in comma delimited form.There are multiple ways to get the desired result. One of the ways is to use FOR XML PATH. Let’s understand with example how we can get the desired result.

Let’s create a master table to store qualifications and insert some qualification in it using the script below.

CREATE TABLE #tbQualification  

(

 QualificationId INT IDENTITY(1,1) PRIMARY KEY ,

 QualificationName  VARCHAR(50)

)

INSERT INTO  #tbQualification (QualificationName) 

VALUES

('MCA'), 

('M.TECH'),

('B.TECH'),

('BCA'),

('MBA')

Check inserted qualifications

SELECT * FROM #tbQualification

Query result: 

QualificationId	QualificationName
1	MCA
2	M.TECH
3	B.TECH
4	BCA
5	MBA

Now create a table to store qualifications of employee.

CREATE TABLE #tbEmployeeQualifications 

(

  Id INT IDENTITY(1,1) PRIMARY KEY , 

  EmployeeId INT, 

  QualificationId INT

)

INSERT INTO  #tbEmployeeQualifications (EmployeeId,QualificationId) 

VALUES

(1,4),

(1,5),

(2,2), 

(2,3),

(3,4),

(3,1), 

(3,5),

(5,4);

Query to get employees and their qualification.

SELECT E.EmployeeId,E.EmployeeName, Q.QualificationName FROM #tbEmployee E

LEFT OUTER JOIN #tbEmployeeQualifications EQ ON E.EmployeeId=EQ.EmployeeId

LEFT OUTER JOIN #tbQualification Q ON Q.QualificationId=EQ.QualificationId

Query Result: 

EmployeeId	EmployeeName	QualificationName
1	AMAN	BCA
1	AMAN	MBA
2	RAMAN	M.TECH
2	RAMAN	B.TECH
3	ANUJ	BCA
3	ANUJ	MCA
3	ANUJ	MBA
4	KABEER	NULL
5	AAMIR	BCA

As we can see there are multiple rows if any employee has multiple qualifications.

But suppose we want to display single record for each employee with multiple qualifications as a comma delimited string then we can write the query as:

Query to get multiple qualifications as a comma separated string of each employee:

SELECT E.EmployeeId,E.EmployeeName,

    STUFF

    (

        (

            SELECT ', ' + CAST(Q.QualificationName AS VARCHAR(50))

            FROM #tbEmployeeQualifications EQ

            LEFT OUTER JOIN #tbQualification Q ON Q.QualificationId=EQ.QualificationId

            WHERE EQ.EmployeeId = E.EmployeeId

            ORDER BY Q.QualificationName

            FOR XML PATH('')

        ), 1, 2, ''

    ) AS Qualifications

FROM

    #tbEmployee E

Query Result:

EmployeeId	EmployeeName	Qualifications
1	AMAN	BCA, MBA
2	RAMAN	B.TECH, M.TECH
3	ANUJ	BCA, MBA, MCA
4	KABEER	NULL
5	AAMIR	BCA

 Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.        

• Tweet
• Share
• Share
• Share

Related Post