Return subquery rows as one delimited column

[kingskippus]

I don't know if this is possible, but I haven't been able to find any
information.

I have two tables, for example:

Table 1 (two columns, id and foo)
id foo
--- -----
1 foo_a
2 foo_b
3 foo_c

Table 2 (two columns, t1_id, and bar)
t1_id bar
------ ----
1 bar_a
1 bar_b
1 bar_c
2 bar_d
3 bar_e
3 bar_f

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.

Is this possible, and if so, can someone please point me to how it can
be done?

[Hugo Kornelis]

On 25 Oct 2006 14:40:21 -0700, ki*********@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/tonyro...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

[Alex Kuznetsov]

Hugo Kornelis wrote:

On 25 Oct 2006 14:40:21 -0700, ki*********@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/tonyro...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/

[Hugo Kornelis]

On 26 Oct 2006 14:02:32 -0700, Alex Kuznetsov wrote:

(snip)

>What do you think?

Hi Alex,

I think that I've already summed up my idead pretty well in my previous
post. In short:

NEVER use the undocumented concatenation "tricks" in SQL Server 2000, no
matter how many people tell you to do so and no matter how often you've
seen them go right. They might go wrong and I believe that I've even
seen repro's that show that they WILL go wrong. I didn;t bookmark them,
unfortunately.

ONLY use the FOR XML PATH trick in SQL Server 2005 if you can be sure
that your data won't have any lesser-than, greater-than or ampersand
symbols or other symbols that have special meaning in XML.

In all other cases, use a userdefined function with a cursor ONLY if you
can be sure that the number of rows is so small that the performance
impact won't be too big.

--
Hugo Kornelis, SQL Server MVP

[Anith Sen]

For some alternatives, see:
http://www.projectdmx.com/tsql/rowconcatenate.aspx

--
Anith

[kingskippus]

Wow, thanks a ton for the great info! I'm investigating options from
your replies now!

On Oct 25, 5:40 pm, [e-mail] wrote:

I don't know if this is possible, but I haven't been able to find any
information....

(snip...)

[--CELKO--]

>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. <<
Yes, if you do not mind violating good programming practices. Why do
you wish to destroy First Normal Form (1NF) with a concatendated list
structure? It is the foundation of RDBMS, after all.

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.

This is like getting on a woodworking newsgroup and asking about the
best rocks for driving screws into fine furniture ...

[kingskippus]

--CELKO-- wrote:

Why do
you wish to destroy First Normal Form (1NF) with a concatendated list
structure? It is the foundation of RDBMS, after all.

Maybe, but what I need is a column that returns a string that is a list
of items in a separate view. The goal is to make things easy on the
end users of the data. If they want to iterate over the list of items
and customize the way it's presented, they can. If they just need a
quick list of the items, they can get that, too.

Sometimes there is an ideal way to do things assuming that your users
have the same tools you do at their disposal, and a real-world way
where you have to answer to the demands of what your users really want.
;-)

[KoliPoki]

Without using a cursor but u still need to declare a variable:

DECLARE @foo varchar(900) SELECT @foo = COALESCE(@foo + ',', '') +
CAST(foo AS varchar(120)) FROM tbl1 WHERE foo <'' SELECT
ISNULL(@foo,'') AS foo')

If you're using it in a query u can wrap it in a function and do
"select foo from dbo.fn_foo()".

Or use a cursor and wrap it in a function.

R.


ki*********@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:

Table 1 (two columns, id and foo)
id foo
--- -----
1 foo_a
2 foo_b
3 foo_c

Table 2 (two columns, t1_id, and bar)
t1_id bar
------ ----
1 bar_a
1 bar_b
1 bar_c
2 bar_d
3 bar_e
3 bar_f

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.

Is this possible, and if so, can someone please point me to how it can
be done?