concatenate rows from a select statement

Question

I have a select statement which is something like this

select col1 from table1 where cond1=true

This returns results like

col1
_____
5
5
6
3

but I want to change it to return me

5-5-6-3

Is this possible? If so how? Please let me know how to do this. Thanks in advance for your replies.

Answer 1

You probably could, but why not do this in the application? I don't know what language you're using, but you could do something like this after you get the result of the query (pseudocode):

result = result.join('-')

Answer 2

This does exactly what you want:

declare @ret varchar(500)
set @ret=''
select @ret = @ret + col1 + '-' from test

select substring(@ret, 1, len(@ret)-1)

NOTE: if your col1 is int then I think you will have to use convert or cast to make it varchar.

Answer 3

http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html

SELECT my_column AS [text()]
FROM   my_table
FOR XML PATH('')

Answer 4

If you're on Microsoft SQL Server, you should be able to do it with the REPLACE command.

SELECT col1,
       dynamic_text = REPLACE( 
    ( 
     SELECT 
      DISTINCT phone_col2 AS [data()] 
     FROM 
      table2
     WHERE 
      condition 
     FOR XML PATH ('') 
    ), '', '-')

 FROM table1
WHERE condition

Something like that would probably work.

Answer 5

I'd suggest you do this in front end, for a large number of rows this will be quite slow. SQL Server is extremely slow sting concatenation. You could create a .net function that takes the table and returns the string, that way you can ensure speed.

If there is no performance worry, like its a one time thing etc go with the other solutions above as those are handled in sql

Answer 6

This works as well:

  --Sample Data
DECLARE @Test TABLE
    (
    Col1 int 
    )

INSERT @Test
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5

--The query
SELECT    SUBSTRING(
       (
       SELECT   '-' + CAST(Col1 AS varchar(10))
       FROM   @Test
       ORDER BY  Col1
       FOR XML PATH ('')
       ), 2, 2000) AS MyJoinedColumn

Answer 7

try this:

declare @Test table (rowid int identity(1,1) primary key, col1 int not null)
insert into @test Values (5)
insert into @test Values (5)
insert into @test Values (6)
insert into @test Values (3)


declare @value varchar(500)
set @value=NULL
select @value = COALESCE(@Value+'-'+CONVERT(varchar,col1),convert(varchar,col1)) from @Test

print @value

Answer 8

Under MySQL you can do that easily

select GROUP_CONCAT(col1 SEPARATOR '-') from table1 where cond1=true