求一个对字符串汇总的问题
表数据如下：
ID CharCol
1 A
1 B
1 C
2 B
2 D
不用存储过程，用一个语句，能不能把以汇总成：
ID CharCol_Total
1 A,B,C
2 B,D
[解决办法]
create table T(ID int, CharCol char(1))
insert T select 1, 'A '
union all select 1, 'B '
union all select 1, 'C '
union all select 2, 'B '
union all select 2, 'D '
create function fun(@ID int)
returns varchar(100)
as
begin
declare @re varchar(100)
set @re= ' '
select @re=@re+CharCol+ ', ' from T where ID=@ID
select @re=left(@re, len(@re)-1)
return @re
end
select distinct ID, dbo.fun(ID) as CharCol_Total from T
--result
ID CharCol_Total
----------- --------------------
1 A,B,C
2 B,D
(2 row(s) affected)
[解决办法]
create function f_getstr(@id varchar(64))
RETURNS varchar(8000)
as
begin
declare @s varhcar(8000)
set @s = ' '
select @s = @s+ ', '+CharCol from tb where id =@id
return stuff(@s,1,1, ' ')
end
