Concatenate Rows using FOR XML PATH()
To concat multiple rows of data into single row we can use XML PATH(”).
SELECT ‘,’ + Column_Name … FOR XML PATH(”)
Generates a set of comma separated values, based on combining multiple rows of data from the Column_Name column. It will produce a value ,abc,pqr,
STUFF(…,1,1,”)
Is then used to remove the leading comma that the previous trick generated, see STUFF for details about its parameters.
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
For example :
We will create a Temp table and insert some sample data.
DECLARE @ConcateTest TABLE(Id INT,CityName VARCHAR(20))
INSERT @ConcateTest(Id,CityName)
VALUES(1,’Los Angeles’),(1,’Austin’)
,(2,’Houston’),(2,’ Fort Worth’)
,(3,’New York’),(3,’Dallas’)
,(4,’Atlanta’),(4,’cape town’)
SELECT DISTINCT ID ,
STUFF((SELECT ‘,’+CityName
FROM @ConcateTest CT1
WHERE CT1.ID=CT2.ID
FOR XML PATH(”)),1,1,”) AS CityName FROM @ConcateTest CT2
ID | CityName |
1 | Los Angeles,Austin |
2 | Houston, Fort Worth |
3 | New York,Dallas |
4 | Atlanta,cape town |
January 10, 2017 at 6:39 pm
Really Good information.
LikeLike