Posted by Hugo Kornelis on 10/26/06 20:35

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

[Back to original message]