34,590
社区成员
发帖
与我相关
我的任务
分享
create table test
(id varchar(10),name varchar(10))
insert into test
select 'mx','aaa' union all
select 'mx','bbb' union all
select 'zm','ccc' union all
select 'dy','ggg' union all
select 'dy','eee'
-- 创建函数
create function dbo.fn_test
(@id varchar(10)) returns varchar(50)
as
begin
declare @names varchar(50)
select @names=isnull(@names+',','')+name
from test
where id=@id
return @names
end
-- 输入 mx
select dbo.fn_test('mx') 'names'
/*
names
--------------------------------------------------
aaa,bbb
(1 行受影响)
*/
-- 输入 zm
select dbo.fn_test('zm') 'names'
/*
names
--------------------------------------------------
ccc
(1 行受影响)
*/
-- 输入dy
select dbo.fn_test('dy') 'names'
/*
names
--------------------------------------------------
ggg,eee
(1 行受影响)
*/
with a(id,name) as
(
select 'mx','aaa' union all
select 'mx','bbb' union all
select 'zm','ccc' union all
select 'dy','ggg' union all
select 'dy','eee'
)
select distinct id,stuff((select ','+name from a as b
where a.id=b.id for xml path('')),1,1,'') as tx
from a where a.id='mx'
/**
id tx
---------------
mx aaa,bbb
---------------
**/