select 编号,种类=min(种类)+'-'+max(种类),次数=count(*)
from(
select 编号=1,种类='A'
union all select 1,'A'
union all select 1,'B'
union all select 1,'B'
union all select 2,'A'
union all select 2,'A'
union all select 2,'B'
union all select 2,'B'
union all select 1,'AA'
union all select 1,'AA'
union all select 1,'BB'
union all select 1,'BB'
union all select 2,'AA'
union all select 2,'AA'
union all select 2,'BB'
union all select 2,'BB'
)a group by 编号,left(种类,1)
order by 编号
--如果种类不是固定的规律的,则写个自定义函数实现种类的处理
create function f_str(
@编号 int
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+'+'+rtrim(种类)
from 表 where 编号=@编号
return(stuff(@r,1,1,''))
end
go
select 编号,种类=dbo.f_str(编号),次数=count(*)
from 表 group by 编号