＋设为首页 ＋添加到收藏夹
我有一个表
id name
1 a
2 b
3 c
2 d
3 f
得到结果
id name
1 a
2 b,d
3 c,f
相同IDd的字符的?接
name value
---------------------------------
1 A
2 B
1 C
3 E
2 F
1 G
?果
name value
-----------------------
1 A,C,G
2 B,F
3 E
--生成??数据
create table tname(name int,value varchar(10))
insert into tname select 1,A
insert into tname select 2,B
insert into tname select 1,C
insert into tname select 3,E
insert into tname select 2,F
insert into tname select 1,G
go
--?建用?定?函数
create function f_str(@name int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret =
select @ret = @ret + , + value from tname where name = @name
set @ret = stuff(@ret,1,1,)
return @ret
end
go
--?行??
select name,value=dbo.f_str(name) from tname group by name order by name
--?出?果
/*
name value
---- -----
1 A,C,G
2 B,F
3 E
*/
--?除???境
drop function f_str
drop table tname
go
create function f_str(@id int)
returns varchar(8000)
as
begin
delcare @str varchar(8000)
set @str=
select @str=@str+,+name from 表 where id=@id
return stuff(@str,1,1,)
end
go
--查询
select id,
dbo.f_str(id) as name
from 表
group by id
