1. We have moved to a new forum! There may be a few things not working properly so please let us know if you find a bug. Remember to use the bbCode [ sql ] tag for SQL statements.
New XenForo forum! Google Ads

Return a comma seperated string?

Discussion in 'SQL - Questions and Answers' started by ben, May 5, 2006.

  1. ben Administrator

    How do you return a comma seperated string of all the values in a given column.

    For example if we have:

    col
    ----
    one
    two
    three

    We would like to get: one, two, three
    ben, May 5, 2006
    #1

  2. ben Administrator

    Dialect: Postgresql
    Answer received on the Postgresql IRC channel by scampbell and indigo.

    SQL:

    
SELECT array_to_string(array(SELECT REPLACE(column, ',', '\,') FROM sometable), ',')


    now you have a rll encoded output, , are imbedded data commas and , are field separators. No data corruption on the transfer just turn the , back into , on the receiving side.

    ---
    As indigo noticed the character would also have to be escaped.
    ben, May 5, 2006
    #2

  3. muk07 New Member

    Dialect: MS SQL
    SQL:
    create function f( )
    
returns varchar(8000) as
    
begin
    
declare ps cursor for select col1 from table1
    
declare @comma varchar(1), 
    
  @res varchar(8000), @colvalue varchar(100)
    
set @res=''
    
set @comma=''
    
open ps
    
fetch next from ps into @colvalue
    
while @@fetch_status=0 begin
    
  set @res=@res+@comma+@colvalue
    
  set @comma=','
    
  fetch next from ps into @colvalue
    
end
    
close ps
    
deallocate ps
    
return @res
    
end
    muk07, May 19, 2006
    #3

  4. sumeet haldankar Guest

    Dialect: MS SQL
    SQL:

    
declare @a varchar(100)
    
select @a = isnull(@a,'') + ',' + col  from table
    
select substring(@a,2,len(@a))
    sumeet haldankar, Jun 9, 2006
    #4

  5. pcdinh Guest

    Dialect: OTHER
    You should not do it with SQL only. It only give more overhead on the database server.

    If you use PHP, you can use explode/implode to do it. Check php.net for more details
    pcdinh, Sep 18, 2006
    #5

  6. john Guest

    Dialect: T-SQL
    SQL:
    use pubs
    
go 
    
DECLARE @str NVARCHAR(4000)
    
  -- Forming the comma separated string
    
SELECT @str= COALESCE(@str+',' , '') + (A.stor_name) 
    
FROM (SELECT distinct stor_name = RTRIM(stor_name) FROM stores ) A
    
  -- now show the string
    
Select @str
    john, Oct 5, 2006
    #6

Share This Page