SQL Server 拼接字符串

温习拼接字符串

if object_id('tb') is not null
drop table tb
go
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go

alter function f_str(@id int) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str + ',' + cast(value as varchar) from tb where id = @id
    set @str = right(@str , len(@str) - 1)
    return @str
end

 

select id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 0 , '')
from tb
group by id