Posted by Alex Kuznetsov on 10/26/06 21:02

Hugo Kornelis wrote:
> On 25 Oct 2006 14:40:21 -0700, kingskippus@gmail.com wrote:
>
> >I don't know if this is possible, but I haven't been able to find any
> >information.
> >
> >I have two tables, for example:
> (snip)
> >What I'm shooting for is returning the result of a subquery as a
> >text-delimited column. In this example, using a comma as the
> >delimiter:
> >
> >Recordset Returned:
> >foo bars
> >----- -----
> >foo_a bar_a,bar_b,bar_c
> >foo_b bar_d
> >foo_c bar_e,bar_f
> >
> >I know that it's usually pretty trivial within the code that is
> >querying the database, but I'm wondering if the database itself can do
> >this.
>
> Hi kingskippus,
>
> If you're using SQL Server 2000, then there is no supported set-based
> way to do this (there are methods that seem to work, but they rely on
> undocumented and unsupported functionality so I won't recommend them for
> serious work). The only supported way is to use a user-defined function
> that uses a cursor to read rows from Table 2 with the same t1_id value
> and concatenates them together - this will be very slow!!
>
> For SQL Server 2005, there's a method that uses documented functionality
> only. It is still a form of abuse, since it uses syntax that is actually
> intended to be used for XML output. As such, you'll find that it can
> seriously mangle yoour data if it contains characters that are "special"
> in XML (such as <, >, and &). You'll find a description of this method
> and a sample on Tony Rogerson's blog:
> http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/11/429.aspx.
>
> The recommended way to do this, though, is to assemble the concatenated
> string at the client. The client has to iterate over the rows one by one
> anyway, so it's natural to add this functionality here. I'd only look
> into alternatives if repeatedly sending the same value for the foo
> column over the network takes too much of your network's capacity.
>
> --
> Hugo Kornelis, SQL Server MVP

Hi Hugo,

In my practice it is not unusual when my users just want to display the
query's output in Excel or Crystal Report or another similar tool,
amd they want results real quick.
In this situation I do not want to know if Excel and Crystal are
capable of concatenating strings, I just do it myself on the server
along with writing the query.
This keeps things simple. This keeps all the code in one place. This
allows me to deliver in one hour. IMO string concatenation is not
complex and it alone does not justify adding one more tier to my
solution.
I believe that in my case doing everything in one place makes perfect
business sense.

What do you think?

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[Back to original message]