create table tb(ID int, name varchar(10))
insert into tb values(1, 'A')
insert into tb values(1, 'B')
insert into tb values(2, 'H')
insert into tb values(3, 'CD') 
insert into tb values(1, 'C') 
go

--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + cast(name as varchar) from tb where id = @id
  set @str = right(@str , len(@str))
  return(@str)
End
go

--调用自定义函数得到结果：
select distinct id ,dbo.f_hb(id) as name from tb

drop table tb
drop function dbo.f_hb

/*
id          name    
----------- --------
1           ABC
2           H
3           CD

（所影响的行数为 3 行）
*/