Concatenate Rows using FOR XML PATH()

Posted on Updated on

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

 

Advertisements

One thought on “Concatenate Rows using FOR XML PATH()

    Mangesh said:
    January 10, 2017 at 6:39 pm

    Really Good information.

    Like

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