T-SQL Challenge: Grouped String Concatenation

re: T-SQL Challenge: Grouped String Concatenation

Aaron Alton — Fri, 27 Feb 2009 19:26:24 GMT

What a cool challenge.....

Do you consider a Common Table Expression to be a "temporary object"? It usually hashes out the same, but I find that CTE's can significantly improve readibility.

re: T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Fri, 27 Feb 2009 19:32:37 GMT

Hi Aaron,

Great question, and the answer is no -- a CTE is not a temporary object but rather part of a bigger statement. All of the following (among other things) are most definitely allowed:

CTEs

Derived Tables

Subqueries

I'll even go so far as to say that any relational operator is also allowed, including UNION, UNION ALL, EXCEPT, INTERSECT. If you can't get the whole result in one query, perhaps you can get it in two or more?

As an aside, I would also like to note that you should not rely on any specific data in the target tables. I will be testing on modified (larger) versions of the tables. Same indexes, but different data -- so no "cute" attempts to send me all of the data in a bunch of UNIONed SELECTs (which would be a single statement according to the rules of the game, but wouldn't pass my correctness test and would be a complete waste of time for everyone involved).

re: T-SQL Challenge: Grouped String Concatenation

Cade Roux — Fri, 27 Feb 2009 19:42:38 GMT

Any chance of a known good target output we can use to run tests against?

re: T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Fri, 27 Feb 2009 19:50:23 GMT

Hi Cade,

Sure, I'll get something attached to the post shortly.

re: T-SQL Challenge: Grouped String Concatenation

Matija Lah — Fri, 27 Feb 2009 20:11:05 GMT

Well, there are commas in Product.Name values.

Are we allowed to use semi-colons to separate individual product names?

(I'm OK with commas, personally, but readability my be an issue.)

re: T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Fri, 27 Feb 2009 20:15:48 GMT

Hi Matija,

You're right, there are commas in there, and that was probably a bad choice of delimiter on my part. But we'll stick with it. Please preserve any commas and formatting already in the data. Add only a single additional comma, and no additional white space, in your output.

re: T-SQL Challenge: Grouped String Concatenation

Roy Harvey — Fri, 27 Feb 2009 20:18:06 GMT

In the copy of AdventureWorks that I am working with, the column Production.Product.Name has 214 rows with commas already embedded in the data. (290 rows have no comma.) Do you expect that data to be handled in any particular way? Unlike the specifications you provided the embedded commas all appear to be followed by a blank.

re: T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Fri, 27 Feb 2009 20:18:29 GMT

Update: Sample output is now attached to this post. If your query can match that output, you're already halfway there. See how easy I'm making this?

re: T-SQL Challenge: Grouped String Concatenation

Roy Harvey — Fri, 27 Feb 2009 20:18:52 GMT

Never mind!

re: T-SQL Challenge: Grouped String Concatenation

Cade Roux — Fri, 27 Feb 2009 20:40:03 GMT

Looks like we're leaving out customers without order, correct?

re: T-SQL Challenge: Grouped String Concatenation

Peso — Fri, 27 Feb 2009 20:42:54 GMT

Your example file for TotalDollarAmount is erraneous.

For Customer ID 1 (Orlando Gee), which has 4 orders (SO43860, SO44501, SO45283 and SO46042), the TotalDollarAmount is

13216.0537 + 23646.0339 + 34066.1881 + 31423.5209 = 102351.7966

Not 1967445.6889

re: T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Fri, 27 Feb 2009 20:55:15 GMT

Cade: Yes

Peso: Just making sure you were paying attention <g> ... the file has been updated with correct values. Thanks for pointing that out!

re: T-SQL Challenge: Grouped String Concatenation

Cade Roux — Fri, 27 Feb 2009 21:07:09 GMT

I'm sorry to say I'm not terribly familiar with the AdventureWorks schema. What's the rule for choosing Store Contacts for a customer of store type? I've got the "hard stuff" working, but I'm having trouble selecting a First Name and Last Name for "Store" type customers who have multiple contacts, like CustomerID = 2:

FirstName LastName

Keith Harris

Geraldine Spicer

So I'm ending up with multiple rows for some customers.

I find there are some customers with both owners and purchasing managers. I didn't see any useful views or UDFs in the database.

re: T-SQL Challenge: Grouped String Concatenation

Peso — Fri, 27 Feb 2009 21:11:15 GMT

A dumb question.

Where do I send my solution?

re: T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Fri, 27 Feb 2009 21:11:22 GMT

Hi Cade,

Just go straight to Person.Contact with the ContactID.

re: T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Fri, 27 Feb 2009 21:17:38 GMT

Peso:

[my first name] [at] [this site]

re: T-SQL Challenge: Grouped String Concatenation

Cade Roux — Fri, 27 Feb 2009 21:20:36 GMT

You're talking about ContactID in SalesOrderHeader? It turns out there's no variation within CustomerID:

SELECT CustomerID

FROM Sales.SalesOrderHeader AS SOH

GROUP BY CustomerID

HAVING MIN(ContactID) <> MAX(ContactID)

but that's probably either a bad DB design or a potential assumption loophole.

re: T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Fri, 27 Feb 2009 21:23:27 GMT

OK, then there is no problem :-)

Just use the ContactID to get the names, not the CustomerID, and you'll be fine.

re: T-SQL Challenge: Grouped String Concatenation

Peso — Fri, 27 Feb 2009 21:41:03 GMT

Just for comparison, how many seconds and how many reads do your best attempt use?

re: T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Fri, 27 Feb 2009 21:54:22 GMT

Peso,

Part of the challenge is wondering whether you've found the best solution. If post my best and you do worse, you'll know -- and that's not nearly as fun... Plus, I don't want anyone thinking that my best is necessarily THE best and only trying to go that far. So I will share numbers only at the end.

re: T-SQL Challenge: Grouped String Concatenation

Peso — Fri, 27 Feb 2009 21:57:09 GMT

Fair enough.

re: T-SQL Challenge: Grouped String Concatenation

RBArryYoung — Sun, 01 Mar 2009 19:54:48 GMT

Hmm, well unless you are intentionally baiting us, you are either really going to like my solution or really, really hate it. :-).

re: T-SQL Challenge: Grouped String Concatenation

Michael Zilberstein — Mon, 02 Mar 2009 07:34:12 GMT

You Excel output example is sorted by CustomerID column - it it a requirement?

re: T-SQL Challenge: Grouped String Concatenation

Brian Tkatch — Mon, 02 Mar 2009 14:51:35 GMT

AdventureWorks: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

re: T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Mon, 02 Mar 2009 14:58:52 GMT

Michael: No, there is no sorting requirement.

re: T-SQL Challenge: Grouped String Concatenation

Peso — Mon, 02 Mar 2009 17:29:08 GMT

I am looking forward to the presentation of results, together with solutions.

There are always new things to learn!

re: T-SQL Challenge: Grouped String Concatenation

RBarryYoung — Tue, 03 Mar 2009 03:14:27 GMT

I thought all of you folks would be at the MVP Summit?

re: T-SQL Challenge: Grouped String Concatenation

daveballantyne — Tue, 03 Mar 2009 10:37:03 GMT

Which Adventure works should we be using ?

Ive downloaded AdventureWorksDB.msi from the Codeplex site Brian gave

but ContactID 1 is "Gustavo Achong" not "Orlando Gee"

re: T-SQL Challenge: Grouped String Concatenation

daveballantyne — Tue, 03 Mar 2009 12:09:34 GMT

Also , do we have to use the base schema , or are we allowed to add indexes ?

re: T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Tue, 03 Mar 2009 15:49:15 GMT

Dave:

No indexes. Check your query and read all of the comments here; you might be joining incorrectly.

re: T-SQL Challenge: Grouped String Concatenation

Mister Magoo — Sun, 08 Mar 2009 19:49:11 GMT

Have I got the wrong data or have you prefixed all order numbers with "SO"?

Thanks

re: T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Sun, 08 Mar 2009 21:46:19 GMT

I've not prefixed anything. The order numbers are already that way. At least on my end; if yours aren't perhaps you need to reinstall AW before doing this challenge.

SELECT SalesOrderNumber

FROM Sales.SalesOrderHeader

re: T-SQL Challenge: Grouped String Concatenation

Mister Magoo — Mon, 09 Mar 2009 00:16:11 GMT

Thanks Adam, I was stupidly using the SalesOrderID...not familiar with AW data..

Anyway my submission is sent - thanks for a fun challenge.

re: T-SQL Challenge: Grouped String Concatenation

Virgil Rucsandescu — Wed, 11 Mar 2009 05:22:48 GMT

This is indeed quite a challenge. I am looking forward to the presentation of solutions.

re: T-SQL Challenge: Grouped String Concatenation

Dan Holmes — Fri, 13 Mar 2009 12:29:38 GMT

I and seeing that "Gustavo Achong" is contact 1 too. No join on this one just SELECT ... FROM Person.Contact WHERE ContactID = 1

I wasn't worried about the difference until i read your response.

The Grouped String Concatenation Challenge is Closed

Adam Machanic — Mon, 16 Mar 2009 00:41:49 GMT

Just over two weeks ago I posted the Grouped String Concatenation Challenge . A more difficult challenge

We need an OLAP function for string concatenation

Alexander Kuznetsov — Sun, 29 Mar 2009 18:39:25 GMT

String concatenation in SQL is frequently discussed on newsgroups and blogs. Adam Machanic recently posted

re: T-SQL Challenge: Grouped String Concatenation

Peso — Wed, 08 Apr 2009 07:12:53 GMT

Out of curiosity I checked out http://msdn.microsoft.com/en-us/subscriptions/renew.aspx and found the only MSDN subscription matching the prize sum was "Visual Studio Team System 2008 Team Suite with MSDN Premium".

It's fantastic!

re: T-SQL Challenge: Grouped String Concatenation

GATTU KUMAR — Mon, 11 May 2009 02:48:47 GMT

Respected Sir,

Sir presently i am working in onsite as junior database developer.I have been facing problem with Strings Concantenation.Sir i did one example

in that i am getting error.plese solve this example.

Example:

Declare @a as varchar(1000)

set @a='select e.empno,e.firstname'+' ' +'e.lastname as ename,e.salary from emp e'

exec (@a)

Output:incorrect syntax near '.' iam unable to solve this problem

plese correct this error.and send url to me the topic about strings Concantenation hopes u to send replay to me

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

Adam Machanic — Sun, 31 May 2009 20:58:01 GMT

After weeks of putting it off, I finally found the time and spent the last day and a half judging the

re: T-SQL Challenge: Grouped String Concatenation

JAY — Thu, 03 Dec 2009 19:15:39 GMT

WHERE CAN I SEE THE ENTRERED / WINNER'S SOLUTIONS???

re: T-SQL Challenge: Grouped String Concatenation

Owais Ahmed — Wed, 06 Apr 2011 16:20:45 GMT

Very easy challenge for me. Did you not have tough challenges?