CODEWITHSUNDEEP
sqlsql-server-2005
Vinod
Vinod

Reputation: 32911

concatenate rows from a select statement

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.

Upvotes: 2

Views: 7034

Answers (8)

vava
vava

Reputation: 25411

Under MySQL you can do that easily

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

Upvotes: 0

KM.
KM.

Reputation: 103697

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

Upvotes: 0

Aaron Alton
Aaron Alton

Reputation: 23236

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

Upvotes: 0

Shafqat Ahmed
Shafqat Ahmed

Reputation: 2072

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

Upvotes: 0

James Skidmore
James Skidmore

Reputation: 50338

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

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

Upvotes: 3

Jason
Jason

Reputation: 1183

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.

Upvotes: 1

TheVillageIdiot
TheVillageIdiot

Reputation: 40527

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.

Upvotes: 3

Sasha Chedygov
Sasha Chedygov

Reputation: 130967

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('-')

Upvotes: 1

Related Questions