As you may know, SQL Server does not have native support for handing arrays. This post is part one of a two part series, where I will look at the most widely used methods to concatenate column values. In this post, I will focus on creating delimited column values and I will explore the performance implications of each method, in the second part of this series.
The two methods that are most often used, in SQL Server 2005+, to concatenate column values are, the variable assignment method and the FOR XML PATH method. The variable assignment method is most commonly used in scalar UDF's, so there are usually some performance implications. The FOR XML PATH method is more commonly used in a correlated sub query to concatenate column values inline. This method also has performance implications.
Let’s create a sample table with data.
IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 't')
DROP TABLE dbo.t;
CREATE TABLE t(
INSERT INTO t VALUES (1,'a');
INSERT INTO t VALUES (1,'b');
INSERT INTO t VALUES (1,'c');
INSERT INTO t VALUES (1,'d');
INSERT INTO t VALUES (2,'e');
INSERT INTO t VALUES (2,'f');
INSERT INTO t VALUES (3,'g');
INSERT INTO t VALUES (4,'h');
We have our data so let’s start with the scalar UDF code. Essentially we need to create a scalar UDF that takes a surrogate key parameter and will use variable assignment to concatenate all required values.
Here is the Scalar UDF code:
CREATE FUNCTION dbo.ConcatenateCols(@Id INT)
DECLARE @RtnStr VARCHAR(MAX)
SELECT @RtnStr = COALESCE(@RtnStr + ',','') + col
WHERE id = @Id AND col > ''
Now that we have our function we can test the scalar UDF code.
dbo.ConcatenateCols(id) AS Cols
Here are the results:
Now let’s take a look at the FOR XML PATH method.
SELECT ',' + CONVERT(VARCHAR(5),col)
FROM t t2
t1.id = t2.id AND t2.col > ''
FOR XML PATH('')
,1,1,'') AS Cols
FROM [t] t1
Note: You can also use CROSS APPLY and achieve the same result, but it makes the code a little easier to read, in my opinion, as shown below:
MAX(STUFF(t2.x_id,1,1,'')) AS Cols
FROM t t1
SELECT ',' + t2.col
FROM t t2
WHERE t2.id = t1.id AND t2.col > ''
FOR xml PATH('')
) AS t2 (x_id)
There you have it. I have shown you the two most widely used methods to concatenate strings in SQL Server 2005+. They are pretty simple to implement and maintain, but one should know how each method performs, so stay tuned…..in the coming post, I will look at the performance implications associated with the scalar UDF and FOR XML PATH methods.
****************** UPDATE ******************
There was a bug in the code that would cause the concatenated value in the variable assignment method to become NULL. When I was creating the sample code, I really didn’t think about using COALESCE and a filter predicate because I was using NON NULL values. Best practice is to use coalesce or a proper predicate filter to prohibit useless data from being concatenated. I have opted to go with a predicate filter of > ‘’. Thanks to all for pointing this out.