Concatenating T-SQL data using XML

Recently I was tasked with concatenating data from multiple rows in a table, to form one single row containing comma separated values for each different ID in a dataset.

Below is a screenshot of the original data I had to work with, along with an example of the code I used, followed by a screenshot of the result set.

As you can see,  my plan involved using a CTE to get a distinct list of ID’s and Values and then for each ID, I used the FOR XML Path Clause to return one record containing all Values in the CTE prefixed with a comma and a space. I then used the stuff function to replace first character in the results with an empty character therefore removing the prefixed comma at the start of the string.

Source:

image

Source code:

   1: WITH CTE AS (

   2:             SELECT     ID, Value

   3:             FROM       #TMP

   4:             )

   5:  

   6: SELECT DISTINCT

   7:             ID

   8:             ,STUFF(

   9:                     (

  10:                       SELECT      ', ' + Value

  11:                       FROM        CTE t1

  12:                       WHERE       t1.id = t2.id

  13:                       FOR XML PATH('')

  14:                     ) ,1,1,''

  15:                    ) AS Txt   

  16: FROM        CTE t2

  17: GROUP BY    ID

Results:

image

Advertisements

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