字符合并的问题---stuff函数的使用

有表如下 ：

年级 班 科目  老师

-------------------------

初一 1 语文 张三
初一 3 语文 张三
初一 1 美术 李四
初二 1 美术 李四
初二 2 美术 李四
初二 3 美术 李四
初二 5 美术 李四
初二 6 美术 李四
初二 7 美术 李四
初二 8 美术 李四
初二 9 美术 李四
初二 10 美术 李四
初二 11 美术 李四
初二 12 美术 李四
初二 13 美术 李四
初二 15 美术 李四
初二 11 物理 李四
初一 2 数学 王五
初二 3 音乐 王五
初二 4 音乐 王五
初二 5 音乐 王五
初二 6 音乐 王五
初二 7 音乐 王五
初二 8 音乐 王五
初二 9 音乐 王五
初二 10 音乐 王五

要实现以下效果：

老师      任课明细

张三       初一 1，3班，语文

李四       初一 1班，美术; 初二 1-3，5-13，15班，美术,初二 11班 物理

王五       初一 2班，数学; 初二 3-10班，音乐

 

实现方法：

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 '初二',1,'美术','李四' union all
select '初二',2,'美术','李四' union all
select '初二',3,'美术','李四' 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 '初二',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,'音乐','王五'    --select * from @t
;with maco as (
select row_number() over(partition by 年级 ,科目,任课教师  order by 班 )as rid,* from @t
)   --select * from maco
,maco1 as (
select 年级,case when min(班)=max(班) then ltrim(min(班))
else ltrim(min(班)) +'~'+ ltrim(max(班)) end 班,
科目,任课教师,(rid-班) a from maco group by 年级 ,科目,任课教师,(rid-班)
)   --select * from maco1
,maco2 as(
select 年级,科目,任课教师 ,班=  stuff((select ','+班 from maco1 where 年级=t.年级 and 科目=t.科目 and 任课教师=t.任课教师
order by substring(班,1,1) for xml path('')),1,1,'') from maco1 t group by 年级,科目,任课教师
)  --select * from maco2
,maco3 as(
select 年级,任课教师 ,班= stuff((select ','+班+'班 '+科目 from maco2 where 年级=t.年级 and 任课教师=t.任课教师
order by 班 for xml path('')),1,1,'') from maco2 t group by 年级,任课教师
) --select * from maco3
,maco4 as(
select 任课教师 ,班= ltrim(stuff((select ';'+'  '+年级+' '+班+' ' from maco3 where 任课教师=t.任课教师
order by 年级 for xml path('')),1,1,'')) from maco3 t group by 任课教师
) select * from maco4

/*

任课教师 班
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
李四   初二 11班 物理,15,1~3,5~13班 美术 ;  初一 1班 美术
王五   初二 3~10班 音乐 ;  初一 2班 数学
张三   初一 1,3班 语文

*/