Solved

How to concatenate rows into comma seperated string

Posted on 2008-10-14
8
3,342 Views
Last Modified: 2011-05-11
Hi
I use microsoft SQL server 2000
I have a table of person names and I have the follwing SQL

SELECT FirstName FROM Names

This gives me one column with a row for each person.
Now I want the query to return only one row containing a comma seperated string of all the firstnames.

Is this possible?
thanks.
0
Comment
Question by:liversen
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 1

Assisted Solution

by:Kumawat_Shobha
Kumawat_Shobha earned 200 total points
ID: 22709767
try in this way
declare      @str      varchar(60)

select      @str = ''

select      @str      = FirstName + ', ' + @str
from      Names

select      left(@str,len(@str)-1)
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22709796
you can do it using the for xml clause
i don't know the exact syntax but i know it can be done
0
 
LVL 1

Author Comment

by:liversen
ID: 22709845

That does it...more or less
the problem is that the table contains many names (4000) and I have tried to set the  @str to varchar(8000) which seems to be the largest size for a varchar. Still it can not hold all names and the string gets truncated.
Is there another datatype I can use instead or will I have do a loop and get a parts of the names ?

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 300 total points
ID: 22709880
     declare @res varchar(8000)
      select @res = coalesce(@res + ',', '') + FirstName
      from Names
        seelct @res

0
 
LVL 1

Expert Comment

by:Kumawat_Shobha
ID: 22710053
User Varchar(max) if you are working on sql server 2005..
if no then let me know i'll work for other solution as well
0
 
LVL 1

Author Comment

by:liversen
ID: 22710141
It's SQL 2000  :o(
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22710274
you could try to create a table with a text / image data type and insert the result of the xml query into that column
you can search the web on how to insert the result of for xml query into a table
it is done using the OPENQUERY clause
0
 
LVL 1

Author Closing Comment

by:liversen
ID: 31505812
I have split the points 200 to Kumawat_Shobha because that gave me a working example 300 to aneeshattingal because the solution is more elegant.
The way I have solved the problem with varchar max size of 8000 is to split my query into multiple where i take 500 names at a time.
Thanks everybody
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question