Monday, June 29, 2009

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:

image

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:

image

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.

13 comments:

Anonymous said...

Thank you for the post!

In the SELECT-statement where you test the UDF, you use the wrong UDF-name: it should be 'ConcatenateCols', not 'ConcatenateColumn'.

Naomi said...

Please check this thread http://tek-tips.com/viewthread.cfm?qid=1599177&page=1

There is a bug in the UDF code - you need to add a check for NULL in Col.

Adam Haines said...

Hi Naomi,

I agree that the code should have a coalesce on the column being concatenated. In my sample here it didnt need the COALESCE function because I was concatenating non NULL values. I will modify the post to include coalesce.

Thanks,
Adam

Term Papers said...

I have been visiting various blogs for my term papers writing research. I have found your blog to be quite useful. Keep updating your blog with valuable information... Regards

daspeac said...

I believe you have also heard about the recover illustrator file utility

Anonymous said...

Outstanding work! The data given was very beneficial. I hope you maintain the great work done.
hialeah locksmith
Wellington FL Locksmith
Locksmith Saratoga CA
Locksmith Newark CA
RedwoodCity locksmith
Locksmith Palo Alto
Locksmith Mesquite tx
plano locksmiths
locksmith in hialeah
plano locksmith
hialeah locksmith
plano locksmiths
fort worth locksmith
pembroke pines locksmiths
Locksmith Mesquite
Locksmith Mesquite tx
hialeah locksmiths
hialeah locksmith
locksmith fort worth texas
irvine locksmith
miami locksmiths
locksmith hialeah
locksmiths fort worth
locksmith miami beach

nikeairmaxshoe said...

Be fast to browse the newest styles and lots of affordable outfits and Nike oxygen Max and women's merchandise is not an daily affair, for that reason that in the stress of modern day time girls ordinarily do not possess a complete great offer time and vitality to go buying by means of countless many style malls, and look at special fees will make you exhausted, so which you can acquire a satisfactory goods, call up for to devote lots of time, the show up of on collection buying significantly minimizing the inconvenience of this, now you not just can every one of the sudden go to to lots of goods Nike Air Max Shoes .

nikeairmaxshoe said...

Be fast to browse the newest styles and lots of affordable outfits and Nike oxygen Max and women's merchandise is not an daily affair, for that reason that in the stress of modern day time girls ordinarily do not possess a complete great offer time and vitality to go buying by means of countless many style malls, and look at special fees will make you exhausted, so which you can acquire a satisfactory goods, call up for to devote lots of time, the show up of on collection buying significantly minimizing the inconvenience of this, now you not just can every one of the sudden go to to lots of goods Nike Air Max Shoes .

Nike mercurial soccer cleats said...

Glad to see all the speakers, really wonderful content. Do not know if you like Nike mercurial soccer cleats or Nike soccer cleats and Nike Football Cleats,Nike Mercurial Vapor Superfly with New nike soccer shoes.

Unknown said...

I know this is fairly old, but I thought I'd just point out that the XML version has a side effect that may cause issues: any invalid XML entities are automatically XML encoded. The one I came across is that CR is converted to &#x0D.

Navdeep Bains said...
This comment has been removed by the author.
Adam Haines said...

Navdeep Bains,

In the case of two tables you would want to do an inner join (or whatever the relationship is) inside of the correlated subquery doing the XML concatenation.

China tours said...

Congratulations for this wonderful reading article. I found it very informative and interesting too, I think you are a brilliant writer. I have bookmarked your blog and will return in the future. I want to encourage you to continue that marvelous work, have a great daytime!I am a china tour lover,You can learn more: China vacation packages | China city tours | China Travel Agency