February 20, 2014

SQL Server - Combining multiple row data into single row data with the help of comma

I had to prepare a report where multiple row data should be merged/combine into single row using separated by comma. I found the best solution to solve this is using STUFF function and FOR XML clause of SQL Server.

According to the Microsoft, "STUFF function helps to inserts a string into another string. First of all it deletes a specified length of characters of the first string at the start position and then inserts the second string into the first string at the start position."

Syntax:

STUFF(character_expression, start_length, replacewith_expression)

For Example:

SELECT STUFF('avesh', 2, 3, 'aastha')

Result:
aaasthah

Using FOR XML in select statement returns the result as in XML format where as select query returns results as a rowset. There are different modes in a FOR XML clause, here we will use PATH mode.


DECLARE @Customer TABLE (CustomerId int, CustomerName varchar(50), BranchName varchar(50))

INSERT INTO @Customer VALUES (1, 'Kale', 'drn branch')
INSERT INTO @Customer VALUES (1, 'kale', 'brt branch')
INSERT INTO @Customer VALUES (3, 'Sailo', 'ktm')

Without using STUFF function:

SELECT CustomerId, CustomerName, BranchName = (SELECT ',' + BranchName
    FROM @Customer AS x2 WHERE CustomerId = x.CustomerId    
     FOR XML PATH(''))
FROM @Customer AS x
GROUP BY CustomerId, CustomerName

ORDER BY CustomerID

Result:



Using STUFF function:

SELECT CustomerId, CustomerName, BranchName = STUFF((SELECT ',' + BranchName
    FROM @Customer AS x2 WHERE CustomerId = x.CustomerId    
     FOR XML PATH('')), 1, 1, '')
FROM @Customer AS x
GROUP BY CustomerId, CustomerName
ORDER BY CustomerID

Result: 

No comments: