Concatenate column values into a single string text row in SQL
June 1, 2015
Recently I was working on a SQL script that would pull values for a one-to-many relationship when joining two tables. Rather than having the many row values map to the identifier multiple times, it needed to be formated so there was a single row identifier with a pipe delimited list of the values. So we start with this with:
And we will end up with:
|1||Value1, Value2, Value3|
In order to do that, we are going to select the value we want with the delimiter and use SQL’s XML PATH method to combine the rows together from the two tables
It is important to note the .value(‘.’, ‘VARCHAR(MAX)’) because that is what will perform the XML encoding and allow for special characters such as &, <, and >.
Tags: SQLBack to Posts