468,514 Members | 1,695 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,514 developers. It's quick & easy.

How do I create an in-line comma seperated list

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?
Jul 20 '05 #1
3 5298
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
Jul 20 '05 #2
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
Jul 20 '05 #3
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
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

20 posts views Thread by Hemant Shah | last post: by
reply views Thread by BRINER Cedric | last post: by
7 posts views Thread by MarkoH | last post: by
4 posts views Thread by Sathyaish | last post: by
1 post views Thread by orenbt78 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.