create table tb(id int,no int,msg varchar(10))insert tb select 1,1,'a' union all select 1,2,'b' union all select 1,3,'c' union all select 1,4,null union all select 1,5,'e'alter function dbo.fstr(@id int) returns varchar(100) as begin declare @str varchar(100) set @str='' select @str=@str+','+isnull(msg,'') from tb where id=@id and not msg is null return stuff(@str,1,1,'') endselect id,dbo.fstr(id) msg from tb group by id
create table A ( id int, no int, msg varchar(10) )insert A select 1,1,'a' insert A select 1,2,'b' insert A select 1,3,'c' insert A select 1,4,null insert A select 1,5,'e' create Function TTT(@id int) returns varchar(1000) as begin declare @T varchar(1000) set @T='' select @T=@T + msg +',' from A where id=@id and msg is not null set @T=left(@T,len(@T)-1) return @T endselect id, dbo.TTT(id) from A group by id
union all select 1,2,'b'
union all select 1,3,'c'
union all select 1,4,null
union all select 1,5,'e'alter function dbo.fstr(@id int)
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=@str+','+isnull(msg,'') from tb where id=@id and not msg is null
return stuff(@str,1,1,'')
endselect id,dbo.fstr(id) msg from tb group by id
(
id int,
no int,
msg varchar(10)
)insert A select 1,1,'a'
insert A select 1,2,'b'
insert A select 1,3,'c'
insert A select 1,4,null
insert A select 1,5,'e'
create Function TTT(@id int)
returns varchar(1000)
as
begin
declare @T varchar(1000)
set @T=''
select @T=@T + msg +',' from A where id=@id and msg is not null
set @T=left(@T,len(@T)-1)
return @T
endselect id, dbo.TTT(id) from A group by id
id msg
1 a,b,c,e拆成
id msg
1 a
1 b
1 c
1 e