Find a summary of strings

cq11123

The table data is as follows:
ID CharCol
1 A
1 B
1 C
2 B
2 D

Instead of storing procedures, using a single statement, can you summarize them into:

ID CharCol_Total
1 A, B, C
2 B, D

fanfan1980

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)

lisongfox

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