Grouped String Concatenation: ... The Winner Is ...

re: Grouped String Concatenation: ... The Winner Is ...

Roman Nowak — Mon, 01 Jun 2009 02:14:02 GMT

Frankly, it's a little unclear to me why you didn't choose Leonid as winner. Logical IO is considered to be a poor metric (http://sqlblog.com/blogs/joe_chang/archive/2008/09/10/why-logical-io-is-a-poor-performance-metric.aspx). Don't you agree? After all it is Leonid solution which proved to be faster.

What's more having done a qick dirty check on i7 (no HT) and Peter's solution is about 70% slower on this config (SQL 2005) - 2:21:3906 vs 1:17:6093 - that's a huge difference; so testbed seems to have a big influance here.

I hope it's not rude to have such a contrary opinion - your rules, you judge - but it just an opinion you asked for.

re: Grouped String Concatenation: ... The Winner Is ...

Dave Ballantyne — Mon, 01 Jun 2009 10:47:31 GMT

Not that I wish to nit pick but , how were my entries deemed to have "order numbers not properly sorted" ?. In both I ordered by OrderId, where as Peter's winning entry order by OrderNumber. The end result is the same though as OrderNumber is a Computer column based upon OrderId !. Or can you point me to a customer id where there is a different order. A quick eyeball of the top 10 rows shows no difference. Either way quite happy with the performance of my queries though.

re: Grouped String Concatenation: ... The Winner Is ...

Adam Machanic — Mon, 01 Jun 2009 12:32:12 GMT

Roman: Yup, my rules, my verdict. I had to choose one, so I did, based on one of the available metrics.

Dave: Yes, it's a computed column, but not explicitly sorting on the right thing seems to mess with the results. Try running my expansion script and you can see this for yourself. Why did you decide to sort on the ID column rather than the column you actually wanted the output sorted by?

re: Grouped String Concatenation: ... The Winner Is ...

Alejandro Mesa — Mon, 01 Jun 2009 14:25:24 GMT

Congrats to Peter Larsson.

Thanks Adam; I enjoyed the fun.

AMB

re: Grouped String Concatenation: ... The Winner Is ...

Adam Machanic — Mon, 01 Jun 2009 14:45:54 GMT

Update on the sorting issue, which disqualified several promising entries: The problem seems to be that the SalesOrderNumber column is not padded. It's just a concatenation of 'SO' and the number converted to NVARCHAR. My expansion script puts a lot of new numbers into the table, so we get the following situation:

SalesOrderID - Ordered

234

456

123456

SalesOrderNumber - Ordered

SO123456

SO234

SO456

re: Grouped String Concatenation: ... The Winner Is ...

Virgil Rucsandescu — Mon, 01 Jun 2009 16:06:33 GMT

Hi Adam,

I couldn't find my name anywhere (checked Round 1 Eliminated, Round 2 Eliminated, Round 3 Eliminated). Are you sure you considered my script? Now I don't have access to my home home Outlook, but home I should have the message I sent you...

Was there any

re: Grouped String Concatenation: ... The Winner Is ...

Adam Machanic — Mon, 01 Jun 2009 16:26:35 GMT

Hi Virgil,

I just checked my e-mail archive and don't see anything from you. Are you sure you sent it? If so, did you make sure to follow the rules exactly? I had a SPAM exclusion for the correct subject line--if you didn't specify it properly your e-mail may have been discarded.

re: Grouped String Concatenation: ... The Winner Is ...

Jacob Bennett — Mon, 01 Jun 2009 18:46:12 GMT

Hi all,

I'm trying to find the error in my query that made the ProductNames not order correctly but I can't seem to find the problem. I've compared my results to Peter's and my output matches his. Any help would be much appreciated. My submission is in the "Round1_Eliminated" folder in Adam attachment.

re: Grouped String Concatenation: ... The Winner Is ...

Adam Machanic — Mon, 01 Jun 2009 19:36:41 GMT

Jacob,

The problem is that you put the ORDER BY in a derived table under the FOR XML PATH, instead of incorporating it as part of the same query. TOP 100 PERCENT w/ ORDER BY is optimized out in derived tables and views so it's effectively meaningless.

re: Grouped String Concatenation: ... The Winner Is ...

Jacob Bennett — Mon, 01 Jun 2009 20:54:42 GMT

Ah yes, old habits die hard.

re: Grouped String Concatenation: ... The Winner Is ...

virgilrucsandescu — Mon, 01 Jun 2009 23:12:52 GMT

Hi Adam,

I forwarded few minutes ago my initial e-mail to you: (adam) (at) (this site) - I hope this is the right address ... Could you please take a look at it - I am curious what was wrong with the message, and it would be great if you could check whenever you have a little time my script (I am not competing against anybody here, just learning). Thank you so much!

re: Grouped String Concatenation: ... The Winner Is ...

Rick Halliday — Mon, 08 Jun 2009 08:54:57 GMT

Adam,

thanks for an interesting challenge. I look forward to reading some of the other solutions to see if there is something I didn't consider or hastily dropped.

Rick

re: Grouped String Concatenation: ... The Winner Is ...

Dean Cochrane — Mon, 29 Jun 2009 20:22:34 GMT

Hey Adam, no complaints from this quarter. Thanks for taking the time (which was substantial, I'll bet) to do this. I'll look through the other solutions. I'm sure I'll find some interesting things in there.

re: Grouped String Concatenation: ... The Winner Is ...

Dean Cochrane — Mon, 29 Jun 2009 20:30:42 GMT

And hey, I don't feel so bad. Some pretty good coders went out in the first round. :)

re: Grouped String Concatenation: ... The Winner Is ...

Jeff Moden — Wed, 22 Jul 2009 02:58:05 GMT

Heh... I guess I'm just missing it. Where is the code from Adam Mechanic? ;-)

"But rather than do the work all alone and simply post my solution..."

re: Grouped String Concatenation: ... The Winner Is ...

Adam Machanic — Mon, 27 Jul 2009 15:04:42 GMT

Jeff, my solutions don't add much, if anything, to the group. My fastest effort was, like everyone else's, based on FOR XML PATH(''). And although I did come up with one "creative" solution based on techniques that no one else used, it performed about as well as the other unique solutions: Dismally.

A bit of self back slapping

SQL and the like — Tue, 08 Sep 2009 09:59:44 GMT

Recently Adam Machanic issued a fresh SQL challenge based upon concatenating string data.  The results

re: Grouped String Concatenation: ... The Winner Is ...

CesarF — Thu, 17 Sep 2009 17:16:40 GMT

followed your contest and congratulate you and all coders.

beside all fun i got...

do you know about some function as simple as:

SELECT concat(StrFld+', ') FROM Table GROUP BY KeyFld

that works with SQL2k?

maybe a c function or so that can be added?

do you know why MS postponed for so long this EVERYDAY functionality?