by Serena
28. September 2009 19:28
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.
Subscribe