create table A
(
col1 int,col2 int,col3 varchar(20)
)
insert A
select 1,2,'a' union
select 1,2,'b' union
select 1,2,'e' union
select 8,3,'t' union
select 8,3,'a' union
select 1,2,'p'
gocreate function f_str(@col1 int,@col2 int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+col3 from A
where col1=@col1 and col2=@col2
return stuff(@str,1,1,'')
end
go--查询
select col1
,col2
,dbo.f_str(col1,col2) as 'col3'
from A
group by col1,col2--删除测试环境
drop function f_str
drop table A--结果
/*
col1 col2 col3
----------- ----------- -----------
1 2 a,b,e,p
8 3 a,t(2 row(s) affected)
*/
(
col1 int,col2 int,col3 varchar(20)
)
insert A
select 1,2,'a' union
select 1,2,'b' union
select 1,2,'e' union
select 8,3,'t' union
select 8,3,'a' union
select 1,2,'p'
gocreate function f_str(@col1 int,@col2 int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+col3 from A
where col1=@col1 and col2=@col2
return stuff(@str,1,1,'')
end
go--查询
select col1
,col2
,dbo.f_str(col1,col2) as 'col3'
from A
group by col1,col2--删除测试环境
drop function f_str
drop table A--结果
/*
col1 col2 col3
----------- ----------- -----------
1 2 a,b,e,p
8 3 a,t(2 row(s) affected)
*/
(
col1 int,col2 int,col3 varchar(20)
)
insert A
select 1,2,'a' union
select 1,2,'b' union
select 1,2,'e' union
select 8,3,'t' union
select 8,3,'a' union
select 1,2,'p'
goCreate table #3 (col1 int,col2 int,col3 varchar(200)) Declare @MinCol1 int
declare @Str varchar(200)select @MinCol1=min(col1) from A
select @str=''while not @mincol1 is null
begin
select @str=@str+col3 from A where col1=@mincol1
--去除最后的逗号
select @str=substring(@str,1,len(@str)-1)
select @str
--写入临时表
insert into #3
select col1,col2,@str from a where col1=@mincol1
group by col1,col2 --处理下一条
select @mincol1=min(col1) from a where col1>@mincol1
endselect * from #3
drop table #3--如果col1相等,而col2可能不等请修改
CAST(MIN(col3) as varchar)+CASE WHEN COUNT(*)=1 THEN ''ELSE ','+CAST(MAX(col3) as varchar)END
FROM aGROUP BY col1,col2
DROP TABLE tb只能连接 两个COL3记录 请高手看看怎么能连接所有的字符串