How do I create an in-line comma seperated list

[Brad Joss]

Scenario:
Table 1 (a id, b name)
Table 2 (a FKid, d value)

A standard join on a gives me something like:

a1 b1 d1
a1 b1 d2

What I want is:

a1 b1 d1,d2

I can easily do this with a function or cursor, but is is somewhat
slow, and I need to do this a lot and I don't really want to have to
maintain tons of functions or cursors.

Thoughts?

[Erland Sommarskog]

Brad Joss (br******@hotmail.com) writes:

Scenario:
Table 1 (a id, b name)
Table 2 (a FKid, d value)

A standard join on a gives me something like:

a1 b1 d1
a1 b1 d2

What I want is:

a1 b1 d1,d2

I can easily do this with a function or cursor, but is is somewhat
slow, and I need to do this a lot and I don't really want to have to
maintain tons of functions or cursors.

This is one of the few cases where a cursor is the best solution.
There are tricks to do this with set-based statements, but they
rely on undocumented and undefined behaviour, and are best avoided.

There is yet another solution: do this client-side if possible.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Diego Buendia]

br******@hotmail.com (Brad Joss) wrote in message news:<ba**************************@posting.google. com>...

Scenario:
Table 1 (a id, b name)
Table 2 (a FKid, d value)

A standard join on a gives me something like:

a1 b1 d1
a1 b1 d2

What I want is:

a1 b1 d1,d2

I can easily do this with a function or cursor, but is is somewhat
slow, and I need to do this a lot and I don't really want to have to
maintain tons of functions or cursors.

Thoughts?

This works but it's limited to the 8000 max varchar size and it's
T-SQL, not pure SQL:

DECLARE @var varchar(8000)

SET @var = ''

SELECT @var = @var + Name + ','
FROM Persons
GROUP BY Name
ORDER BY Name

SELECT Substring( @Var, 1, Len( @Var ) - 1 ) as List

[Erland Sommarskog]

Diego Buendia (db*******@yahoo.es) writes:

This works but it's limited to the 8000 max varchar size and it's
T-SQL, not pure SQL:

DECLARE @var varchar(8000)

SET @var = ''

SELECT @var = @var + Name + ','
FROM Persons
GROUP BY Name
ORDER BY Name

SELECT Substring( @Var, 1, Len( @Var ) - 1 ) as List

Note that this relies on undefined behaviour, and it may or may not
work depending on your statement, indexes etc.

See http://support.microsoft.com/default.aspx?scid=287515. Pay particular
attention to the first section after the subtitle CAUSE.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp