How do I place multiple records of a sub-query in one record?

How do I place multiple records of a sub-query in one record?

Post by U2Ft » Wed, 06 Sep 2006 11:03:03


Hi,

I think this can best be explained with an example. I don't have a specific
case or sample table. I can make one up if you guys want me to.

What I'm trying to do is this:

Let's say there are three snake charmers in Florida, two in California and
one in Texas. I want to end up with three records in my recordset in the
following fashion:

State -- Snake Charmers
Florida -- Jimmy, Joey, Billy
California -- Kenny, Bobby
Texas -- Sammy

as opposed to:
State -- Snake Charmer
Florida -- Jimmy
Florida -- Joey
Florida -- Billy
California -- Kenny
California -- Bobby
Texas -- Sammy

Is there a way for me to get this result in SQL Server as opposed to
processing the recordset in my C# code. Also which is the right way to handle
it? Almost all developers will tell you that the way to handle this is in C#
not in SQL. Just wanted to ask you SQL folks.

--
Thanks,

Sam
 
 
 

How do I place multiple records of a sub-query in one record?

Post by T21uaWJ1en » Wed, 06 Sep 2006 13:26:02

Hah, Getting this question again :) Well, I used to suggest a set based
approach to solve this problem but I was slapped left, right and centre by
the experts in this group to show that it will fail.. And I am happy that
they did :) Some things never get into our head unless forced...
Coming to answer your question, you can use a cursor to loop through the
records and manually concatenate each row, if you are using SQL Server 2000.
And that is the only, I repeat, only approach that will work..

For approaches on SQL Server 2005 (using for XML Path) and pitfalls with
other approaches you might have come across in SQL Server 2000, you may check
this link...

http://www.yqcomputer.com/


--
-Omnibuzz (The SQL GC)

http://www.yqcomputer.com/