declare @T table 
(年级 varchar(4),班 int,科目 varchar(4),任课教师 varchar(4))
insert into @T
select '初一',1,'语文','张三' union all
select '初一',3,'语文','张三' union all
select '初二',1,'美术','李四' union all
select '初二',2,'美术','李四' union all
select '初二',3,'美术','李四' union all
select '初二',4,'美术','李四' union all
select '初二',5,'美术','李四' union all
select '初二',6,'美术','李四' union all
select '初二',7,'美术','李四' union all
select '初二',8,'美术','李四' union all
select '初二',9,'美术','李四' union all
select '初二',10,'美术','李四' union all
select '初二',11,'美术','李四' union all
select '初二',12,'美术','李四' union all
select '初二',13,'美术','李四' union all
select '初二',14,'美术','李四' union all
select '初二',15,'美术','李四' union all
select '初二',11,'物理','李四' union all
select '初一',2,'数学','王五' union all
select '初二',3,'音乐','王五' union all
select '初二',4,'音乐','王五' union all
select '初二',5,'音乐','王五' union all
select '初二',6,'音乐','王五' union all
select '初二',7,'音乐','王五' union all
select '初二',8,'音乐','王五' union all
select '初二',9,'音乐','王五' union all
select '初二',10,'音乐','王五'

;with maco as
(
select row_number() over (partition by 年级,科目,任课教师 order by 班) as rid,* from @T
)
,maco1 as(
select 
年级,case when min(班)=max(班) then 
ltrim(min(班)) else ltrim(min(班))+'-'+ltrim(max(班)) end as 班,
科目,任课教师 
from maco group  by 年级,科目,任课教师,rid-班
)
,maco2 as
(
    select 年级,科目,任课教师,班=stuff((select ','+班 from maco1 t 
    where 年级=b.年级 and 科目=b.科目 and 任课教师=b.任课教师 
    order by 任课教师,班 for xml path('')), 1, 1, '') 
    from maco1 b group by 年级,科目,任课教师
)
,maco3 as
(
    select 年级,任课教师,科目=(select ''+班+科目 from maco2 t 
    where 年级=b.年级 and 任课教师=b.任课教师 
    order by 任课教师,班 for xml path(''))
    from maco2 b group by 年级,任课教师
)
,maco4 as
(
    select 任课教师,科目=stuff((select '、'+年级+科目 from maco3 t 
    where 任课教师=b.任课教师 
    order by 任课教师 for xml path('')), 1, 1, '') 
    from maco3 b group by 任课教师
)
select * from maco4

/*
任课教师 科目
---- --------------------------
李四   初二11物理1-15美术
王五   初二3-10音乐、初一2数学
张三   初一1,3语文
*/