日期:2014-05-18  浏览次数:20533 次

varchar类型字段的sum功能实现
现有表记录如下;
fcoding         Fmemo
01                   zhang
01                   wang
02                   li
02                   lili
02                   feifei
03.....

想要的结果是
fcoding       Fmemo
01                 zhang;wang;
02                 li;lili;feifei;


------解决方案--------------------
create function f_str(@fcoding varchar(10))
returns varchar(100)
as
begin
declare @str varchar(100)
set @str= ' '
select @str=@str+[text]+ '; ' from 表 where fcoding=@fcoding
return @str
end

GO

select fcoding,dbo.f_str(fcoding ) as Fmemo
from A
group by fcoding
------解决方案--------------------
create table T(fcoding varchar(10), Fmemo varchar(10))
insert T select '01 ', 'zhang '
union all select '01 ', 'wang '
union all select '02 ', 'li '
union all select '02 ', 'lili '
union all select '02 ', 'feifei '

create function fun(@fcoding varchar(10))
returns varchar(200)
as
begin
declare @re varchar(200)
set @re= ' '
select @re=@re+ '; '+Fmemo from T where fcoding=@fcoding

return stuff(@re, 1, 1, ' ')
end

select fcoding, Fmemo=dbo.fun(fcoding) from T group by fcoding

--result
fcoding Fmemo
---------- --------------------------------------------------------------------------------------------
01 zhang;wang
02 li;lili;feifei

(2 row(s) affected)