SQL Server Performance Forum – Threads Archive

String Concatenation

Hi, How can I generate a comma separated list sorted alphabetical, in t-sql?
For e.g. I have this table:
—– —-
index name
—– —-
1 radu
1 michael
2 anna
2 modest
2 alexander
3 modest
3 anna And I want to concatenate those name by the index (and sorted), separated by a comma; without useing cursor. Results:
—– ———
index name
—– ———
1 michael, radu
2 alexander, anna, modest
3 anna, modest Thanks, Jack Bower
software developer
IATAR Studio
http://iatar.port5.com

Check this out:http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
We had a thread here, that this relies on undocumented behaviour in SQL Server, but I can’t find it right now. So, you might also want to search for that. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Also, note that if the concatenated string exceeds 8000 characters, you need to use more than one variable.
If you use front end application, do concatenation there Madhivanan Failing to plan is Planning to fail

Hi Madhivanan,
As this question is posted under 2005 section so in SQL server 2005,
as we know, there is no 8000 chars restriction for varchar datatype.
user can use
declare @var1 varchar(MAX)
where it can hold upto 2GB storage. but as you said it will be better to do concatenation at the front end in this case.

>&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />osted under 2005 section so in SQL server 2005, Thanks. I didnt see where it is posted [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Hi
My concatenated string don’t exceeds 8000 characters, so this will not be a problem. I have less than 200 hundred. FrankKalis, your link helped me. Thanks, Jack Bower
software developer
IATAR Studio
http://iatar.port5.com

]]>