34,576
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据: tt
if object_id('tempdb.dbo.tt') is not null drop table tt
create table tt (uid int,name varchar(2))
insert into tt
select 1,'a' union all
select 2,'a' union all
select 2,'bn' union all
select 3,'g' union all
select 4,'f' union all
select 5,'f' union all
select 6,'h' union all
select 6,'j' union all
select 7,'a' union all
select 7,'b' union all
select 7,'c'
go
--> 合并函数
create function fn_str(@uid int)
returns nvarchar(4000)
as
begin
declare @r nvarchar(4000)
select @r=isnull(@r+',','')+name from tt where uid=@uid
return (@r)
end
go
--> 调用
select uid,name=dbo.fn_str(uid) from tt group by uid
/*
结果
1 a
2 a,bn
3 g
4 f
5 f
6 h,j
7 a,b,c
*/
create function fn_names(
@uid int
)
returns varchar(200)
as
begin
declare @r varchar(200)
select @r=isnull(@r+',','')+name from tt where uid=@uid
return @r
end
go
--查询
select uid,dbo.fn_names(uid) as names
from tt
group by uid
--> 测试数据: #tt
if object_id('tempdb.dbo.#tt') is not null drop table #tt
create table #tt (uid int,name varchar(2))
insert into #tt
select 1,'a' union all
select 2,'a' union all
select 2,'bn' union all
select 3,'g' union all
select 4,'f' union all
select 5,'f' union all
select 6,'h' union all
select 6,'j' union all
select 7,'a' union all
select 7,'b' union all
select 7,'c'
select
*
from
(select distinct uid from #tt) a
outer apply
(select name = stuff((select ',' + name from #tt where uid = a.uid for xml path('')), 1, 1, '')) b
/*
结果
1 a
2 a,bn
3 g
4 f
5 f
6 h,j
7 a,b,c
*/