Concatenating Column Values (Part 1)

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.

USE [tempdb]

GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE  NAME = 't')

BEGIN

    DROP TABLE dbo.t;

END

GO

CREATE TABLE t(

id INT,

col CHAR(1)

);

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:

USE [tempdb]

GO

CREATE FUNCTION dbo.ConcatenateCols(@Id INT)

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @RtnStr VARCHAR(MAX)

SELECT @RtnStr = COALESCE(@RtnStr + ',','') + col

FROM dbo.t

WHERE id = @Id AND col > ''

RETURN @RtnStr

END

GO

Now that we have our function we can test the scalar UDF code.

SELECT 

    id,

    dbo.ConcatenateCols(id) AS Cols

FROM t

GROUP BY

    Id

Here are the results:

Now let’s take a look at the FOR XML PATH method. 

SELECT 

    Id,

    STUFF(

            (

            SELECT ',' + CONVERT(VARCHAR(5),col)

            FROM t t2

            WHERE

                t1.id = t2.id AND t2.col > ''

            FOR XML PATH('') 

            )

    ,1,1,'') AS Cols

FROM [t] t1

GROUP BY

    Id

Results:

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:

SELECT 

    t1.id,

    MAX(STUFF(t2.x_id,1,1,'')) AS Cols

FROM t t1

CROSS apply(

    SELECT ',' + t2.col

    FROM t t2

    WHERE t2.id = t1.id AND t2.col > ''

    FOR xml PATH('')

) AS t2 (x_id)

GROUP BY

    t1.id

GO

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.