declare @test table (ID int, NR varchar(10))
insert @test
select 1, 'DDD' union all
select 1, 'EEE' union all
select 2, 'EEE' union all
select 3, 'TTT'
declare @result varchar(100)
select @result=coalesce(@result+'/', '')+NR from @test where id = 1
select ID = 1, NR = @result
insert @test
select 1, 'DDD' union all
select 1, 'EEE' union all
select 2, 'EEE' union all
select 3, 'TTT'
declare @result varchar(100)
select @result=coalesce(@result+'/', '')+NR from @test where id = 1
select ID = 1, NR = @result
分组汇总字符串——字符串叠加
*/--创建测试数据表
create table test(ID int, NR char(3))
insert test
select 1, 'DDD' union all
select 1, 'EEE' union all
select 2, 'EEE' union all
select 3, 'TTT' union all
select 3, 'VVV'
go
--创建函数
create function fn_test(@Input int)
returns varchar(8000)
as
begin
declare @return varchar(8000)
select @return = coalesce(@return + '/', '') + NR from test where ID = @Input
return(@return)
end
go--使用函数体现结果
select ID, NR = dbo.fn_test(ID) from test group by ID/*
ID NR
1 DDD/EEE
2 EEE
3 TTT/VVV
*/--删除测试环境
drop table test
drop function fn_test