Solved

How to concatenate rows into comma seperated string

Posted on 2008-10-14
8
3,338 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
 
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now