Please disable your adblock and script blockers to view this page
 

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.

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


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.        
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon