Combine Specific Column Value with SQL

is there anywhere to get values like this?

from

id        column_1        column_2
1         abc             hello 
1         abc             world

to

id        column_1        column_2
1         abc             开发者_如何学JAVAhello world

Thanks alot :)

I am assuming that a given value in column_1 doesn't always correspond to the same value in ID and vice versa.

DECLARE @temptable TABLE (id integer, column_1 varchar(20), column_2 varchar(20))

INSERT into @temptable 
select 1 ,'abc' ,'hello' 
UNION 
select 1 ,'abc','world' 
union 
select 1, 'def', 'blah' 
union 
select 2, 'abc', 'world'
union
select 2, 'abc', 'blah'

select id, column_1, 
(SELECT column_2 + ' ' 
 FROM @temptable t2
    where t2.id = t1.id and t2.column_1 = t1.column_1
 FOR XML PATH('') 
 ) new_column_2 
from @temptable t1
group by id, column_1

Results:

id  column_1    new_column_2
1   abc         hello world 
1   def         blah 
2   abc         blah world 

In Mysql, you can use GROUP_CONCAT, something like this:

SELECT id, column_1, GROUP_CONCAT(column2 SEPARATOR ' ') FROM some_table GROUP BY id, column_1

In Sql Server, you use the FOR XMLcontruct for that.

Look at this example here:

SQL Query to get aggregated result in comma seperators along with group by column in SQL Server

Well, to be quite literal about it:

DECLARE @t TABLE (id integer, column_1 varchar(20), column_2 varchar(20))

INSERT into @t
    SELECT 1 ,'abc' ,'hello' 
    UNION  
    SELECT 1 ,'abc','world' 

SELECT DISTINCT 
     id, column_1, 
     (SELECT column_2 + ' ' FROM @t FOR XML PATH('') ) column_2 
FROM @t