create table 表(code varchar(20),num int,strcode varchar(20)) insert into 表 select 'C01001',1,'001' union all select 'C01001',1,'002' union all select 'C01002',1,'005' union all Select 'C01002',2,'008'code num strcode C01001 1 001 C01001 1 002 C01002 1 005 C01002 2 008执行后: code num strcode C01001 2 001,002 C01002 3 005,008go--创建用户定义函数 create function Union_str(@code varchar(20)) returns varchar(8000) as begin declare @ret varchar(8000) set @ret = '' select @ret = @ret+','+strcode from 表 where code = @code set @ret = stuff(@ret,1,1,'') return @ret end go
--执行 select code,sum(num) as num ,strcode=dbo.Union_str(code) from 表 group by code order by code
code num strcode
C01001 1 001
C01001 1 002
C01002 1 005
C01002 2 008执行后:
code num strcode
C01001 2 001,002
C01002 3 005,008
insert into 表 select 'C01001',1,'001'
union all
select 'C01001',1,'002'
union all
select 'C01002',1,'005'
union all
Select 'C01002',2,'008'code num strcode
C01001 1 001
C01001 1 002
C01002 1 005
C01002 2 008执行后:
code num strcode
C01001 2 001,002
C01002 3 005,008go--创建用户定义函数
create function Union_str(@code varchar(20))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+strcode from 表 where code = @code
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select code,sum(num) as num ,strcode=dbo.Union_str(code) from 表 group by code order by code