如何写这样的语句
col1   col2
1         a1
1         a2
2         b1
2         b2
3         c1
3         c2
变为
col1,col2,col3
a1a2,b1b2,c1c2

------解决方案--------------------
create function dbo.uf_getstr(@col1 int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str= ' '
select @str=@str+col2 from 表1 where col1=@col1
return @str
end

drop table 表1
go
create table 表1(col1 int,col2 varchar(10))
insert into 表1
select 1, 'a1 '
union all select 1, 'a2 '
union all select 2, 'b1 '
union all select 2, 'b2 '
union all select 3, 'c1 '
union all select 3, 'c2 '

declare @sql varchar(1000)
set @sql= ' '
select @sql=@sql+ ',max(case when col1= '+rtrim(col1)+ ' then strs end) as col '+rtrim(col1)
from (select distinct col1 from 表1)t
set @sql=right(@sql,len(@sql)-1)
exec( 'select '+@sql+ ' from (select distinct col1,dbo.uf_getstr(col1) strs from 表1)t ')
------解决方案--------------------
create function dbo.uf_getstr(@col1 int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str= ' '
select @str=@str+col2 from 表1 where col1=@col1
return @str
end
------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb(col1 varchar(10),col2 varchar(10))
insert into tb(col1,col2) values( '1 ', 'a1 ')
insert into tb(col1,col2) values( '1 ', 'a2 ')
insert into tb(col1,col2) values( '2 ', 'b1 ')
insert into tb(col1,col2) values( '2 ', 'b2 ')
insert into tb(col1,col2) values( '3 ', 'c1 ')
insert into tb(col1,col2) values( '3 ', 'c2 ')
go

if object_id( 'pubs..f_hb ') is not null
drop function f_hb
go

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

--调用自定义函数得到结果：
select
max(case when col1 = 1 then col2 end) as col1,
max(case when col1 = 2 then col2 end) as col2,
max(case when col1 = 3 then col2 end) as col3
from
(
select distinct col1 ,dbo.f_hb(col1) as col2 from tb
) t

drop table tb

/*
col1 col2 col3
----- ----- ----
a1a2 b1b2 c1c2

（所影响的行数为 1 行）

*/