Concatenating Strings in SQL without a Loop

When you find yourself in a situation where you would like to concatenate the data items for a data object into one line, where the object can be related to many data items that are stored in a separate table, here is a quick way to accomplish that in SQL without expressly using a loop:

• Lines 2-3: declare and initialize variable that will be used to hold the concatenated string
• Line 5: select variable to equal itself plus the string you are interested in and a delimiter such as a comma, if desired
• Lines 6-9: the query that returns the data items for the object you are interested in
• Line 11: trim off the excess delimiter from the concatenated string

The resulting concatenated string:

An example application is to place this type of concatenation in a function where you can submit the ID for the object you are concatenating data items for to the function, and have it return the concatenated string.