Let me start out by saying I came across the following syntax while reading a forum on building dynamic SQL. I can’t find the address right now; but I would like to share the syntax letting you know it is not unique to me.
I love SQL that uses a variable to build something based on a set of data by starting with a variable, and modifying that variable with a single SQL statement. A good example is to build a comma separated string based on multiple records in a table.
I have always started out with a string variable initialized as an empty string. Then I used a sql query to concatenate a value from each record preceded by a comma character.
DECLARE @Result VARCHAR(8000) = ‘’
SELECT @Result += ‘, ‘ + Somevalue
FROM SomeTable
SELECT SUBSTRING(@Results, 3, 8000)
Note that I have to perform the SUBSTRING to drop the first comma & space instance from the beginning of the string. The result should start with the first value from SomeTable.
Here is an alternative version of this pattern I read this last week.
DECLARE @result VARCHAR(8000)
SELECT @result=ISNULL(@result+ ', ','') + SomeValue
FROM SomeTable
SELECT @result
Which option should you use? I guess it would depend on the number of rows in your result set from the query. The ISNULL technique does not require a substring to strip out the undesired comma, but, it does perform an ISNULL change on every row of the table. I’d guess the SUBSTRING method would perform better on a larger result set.
Well, that’s the tip for the day. Now you have a couple different methods for concatenating data from multiple rows into a single varuable.
Cheers,
Ben