Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to concatenate rows into comma seperated string

Posted on 2008-10-14
8
Medium Priority
?
3,352 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 600 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 900 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

876 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