Rowset string concatenation: Which method is best?

re: Rowset string concatenation: Which method is best?

Ulises — Fri, 09 Mar 2007 15:27:37 GMT

There is yet another approach to test, that performance wise seems to provide its advantages:

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +

CAST(Emp_UniqueID AS varchar(5))

FROM SalesCallsEmployees

SELECT @EmployeeList

I saw it on this website: http://www.sqlteam.com/item.asp?ItemID=2368

re: Rowset string concatenation: Which method is best?

Adam Machanic — Sun, 18 Mar 2007 16:16:07 GMT

Hi Ulises,

That should be exactly the same as the CASE expression from a perf POV.

re: Rowset string concatenation: Which method is best?

Peter Larsson — Wed, 28 Mar 2007 07:54:23 GMT

What about this SQL Server 2005 attempt?

-- Prepare sample data

DECLARE @Sample TABLE (ID INT, Code VARCHAR(3))

INSERT @Sample

SELECT 290780, 'LT' UNION ALL

SELECT 290780, 'AY' UNION ALL

SELECT 290781, 'ILS' UNION ALL

SELECT 290780, 'AY'

-- Show the expected output

SELECT DISTINCT s1.ID,

STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES

FROM @Sample AS s1

ORDER BY s1.ID

SELECT DISTINCT s1.ID,

STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES

FROM @Sample AS s1

ORDER BY s1.ID

SELECT DISTINCT s1.ID,

STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES

FROM @Sample AS s1

ORDER BY s1.ID

re: Rowset string concatenation: Which method is best?

Adam Machanic — Tue, 22 May 2007 18:07:23 GMT

Hi Peter,

FOR XML PATH will definitely outperform virtually all other methods. Plus, it's documented/supported. In 2005, it's absolutely the best choice!

re: Rowset string concatenation: Which method is best?

Oscar — Sat, 10 Nov 2007 06:15:20 GMT

You made my day Adam, Thank you VERY much, i was thinking on ussing cursors to obtain each variable field to obtain the ones to concatenate linked to those fields, i didn't code anything because you kept me of doing it, your solution is eficient, fast and easy, i dont have a lot of experience developing applications, i am doing a distribution service management application on asp nowdays, and i really understood your explanation. What book would u suggest me to learn the sql things that they didnt teach me at school, which is the best method to learn sql, besides constant reading?.

Thanks again Adam, and best Regards.

re: Rowset string concatenation: Which method is best?

Anonymous — Fri, 04 Jan 2008 16:12:26 GMT

With SQL 2005 another possibility exists. Custom Aggregate functions using the CLR. The Microsoft documentation even uses concatenation as their example.

re: Rowset string concatenation: Which method is best?

Ashish — Tue, 23 Sep 2008 12:00:58 GMT

Hi Adam

I have been struggling with the below error while trying to run the SSIS from the one of our PRO server.

Error: 2008-09-11 15:14:08.96

Code: 0xC004801F

Source: Data Flow Task Data Flow Task (DTS.Pipeline)

Description: The component metadata for "component "DataReader Source" (157)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.

End Error

I am unable to understand how to remove this error, can you please help me on this?

re: Rowset string concatenation: Which method is best?

Chi — Thu, 30 Oct 2008 20:19:35 GMT

The FOR XML Path version can only concatenate up to 256 characters long. Any way to resolve this limitation?

re: Rowset string concatenation: Which method is best?

Adam Machanic — Mon, 03 Nov 2008 15:07:47 GMT

Hi Chi,

That is not correct. I regularly use it to form CLOB strings, and it can (in theory -- I hope not in practice) go all the way to 2 GB. If you're hitting a 256 character wall, it's either a data type issue (VARCHAR(256)), or your UI that's making it look like you've hit a limit when you really have not.

Bitmask Handling, part 2: Bitmask reconstitution

Adam Machanic — Sun, 25 Jan 2009 19:47:51 GMT

Posting the first part of my series on bitmasks (yes, this is now officially a series) taught me a lot

re: Rowset string concatenation: Which method is best?

Michael C — Mon, 02 Feb 2009 09:16:38 GMT

"And so I set out to prove him wrong..."

Interesting, I would have thought you would set out to find the best solution.....

re: Rowset string concatenation: Which method is best?

CERU — Thu, 12 Feb 2009 16:04:30 GMT

FOR XML PATH will convert some of the XML special characters - like & < > - to something else... Any way to get around that?

re: Rowset string concatenation: Which method is best?

Adam Machanic — Fri, 13 Feb 2009 20:39:25 GMT

CERU: Absolutely:

---

DECLARE @t TABLE (x CHAR(1))

INSERT @t

SELECT '&'

UNION ALL

SELECT '<'

UNION ALL

SELECT '>'

SELECT

(

SELECT

(

SELECT x AS [text()]

FROM @t

FOR XML PATH(''), TYPE

) AS r

FOR XML RAW, TYPE

).value('(/row/r)[1]', 'varchar(max)')

---

re: Rowset string concatenation: Which method is best?

CERU — Thu, 19 Feb 2009 14:44:50 GMT

Great!.. Thanks

I read somewhere that FOR XML will be removed in future version.. that is a shame since I just found how to do this.. Thanks for your help!

re: Rowset string concatenation: Which method is best?

Adam Machanic — Thu, 19 Feb 2009 15:40:14 GMT

Hi CERU,

FOR XML EXPLICIT is deprecated, but FOR XML is not and I don't expect it to be removed anytime in the foreseeable future. XML is an absolutely vital technology at this point, and the FOR XML syntax -- especially the FOR XML PATH variant -- is one of the key elements of SQL Server's XML support. Since it's not currently on the deprecation path it has to stay around for at least four more major versions of the product, so it should be with us for at least 10 more years at a minimum. And I certainly don't expect it to be deprecated in SQL11, so we can expect it to be around for a lot longer than that. So get comfortable with it... It's here to stay.

re: Rowset string concatenation: Which method is best?

RBArryYoung — Mon, 23 Feb 2009 18:01:25 GMT

Adam:

Here's the FOR XML fix that I have been using, it seems simpler and appears to work just as well. Have I missed anything?

--=====

select (

SELECT n + ','

FROM (

SELECT 'a<b' AS n

UNION ALL

SELECT 'b>a'

UNION ALL

SELECT 'b&a'

UNION ALL

SELECT 'b

a') r

FOR XML PATH(''), TYPE

).value('.[1]','varchar(max)')

--=====

re: Rowset string concatenation: Which method is best?

Adam Machanic — Mon, 23 Feb 2009 19:25:55 GMT

Looks good to me, RBarryYoung. Did you come up with that, or is there someone we can credit? I asked a group of MVPs how to solve the problem and the best anyone came up with is the version I posted to CERU (credit goes to Tony Rogerson for coming up with it). The one you posted is certainly better and when I use it I would love to know to whom I should attribute it.

T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Fri, 27 Feb 2009 18:24:55 GMT

It's been quite a while since the LIKE vs ? Puzzle , and I feel like it's time for another one. Response

re: Rowset string concatenation: Which method is best?

RBArryYoung — Sun, 01 Mar 2009 19:19:52 GMT

I came up with it after I saw the prior version that you posted here and over at Ward Pond's blog also.

re: Rowset string concatenation: Which method is best?

Ali Delshad — Fri, 08 May 2009 07:22:54 GMT

Hey man

Thank you very much :)

You make my work too much easier

re: Rowset string concatenation: Which method is best?

JRK — Tue, 30 Mar 2010 15:27:14 GMT

RBArryYoung...

Nice one!.

T-SQL Tuesday # 16 : This is not the aggregate you're looking for

Aaron Bertrand — Tue, 08 Mar 2011 21:46:13 GMT

This week, T-SQL Tuesday is being hosted by Jes Borland ( blog | twitter ), and the theme is " Aggregate

re: Rowset string concatenation: Which method is best?

Kevin — Tue, 24 May 2011 10:02:12 GMT

Hello Everyone. I tried this lines of query on 2005 and it works just fine. A particular client needs it on version 2000 but it keeps giving syntax error near 'For'. Any help? here is my code:

update tblLabResult

set Specimen = (select distinct stuff((Select '/' + sp.[Name]

from dbo.tblLabSpecimenUsed as su inner join dbo.tblLabSpecimen as sp

on su.SpecimenID = sp.SpecimenID

where su.ResultID = @NResultID

order by su.SpecimenID

for xml path('')),1,1,'') as userlist

from tblLabSpecimenUsed

)

where ResultID = @NResultID

re: Rowset string concatenation: Which method is best?

sai — Wed, 18 Jan 2012 05:07:41 GMT

FOR XML is not an option when you are working with characters like 0x0018 or ox0016 etc. it throws an exception saying that XML cannot serialize since it has those values. i don't have a clue at this point of time what to do with that. performance wise it will eliminate the need for while loop and i tested it with millions of data and it just works like a charm. can you find out a way for that and also how to add coleasce for the column. thanks

re: Rowset string concatenation: Which method is best?

Adam Machanic — Wed, 18 Jan 2012 16:30:51 GMT

sai: I use REPLACE to get rid of those characters. If you peek at the code for sp_whoisactive you'll see how I've done it. It's ugly but it works.

Not sure what you mean about COALESCE. Can you be more specific?