SQL SQL中加入跨班分組組別名稱

想在以下的SQL中，再加入跨班分組(例如英文2A,2B兩班，其實分了2A1,2B1,2AB3組)的組別名稱，有無辦法？

select
b.timeseq '考績',
a.classlvl,
a.classcode,
a.classno,
a.chname,
c.ch_des,
b.sysscore '成績',
b.omclasslvl '排名'
from vw_stu_lateststudent a
left outer join tb_asr_subjassessdata b on a.suid=b.suid and a.schlvl=b.schlevel and a.schsess=b.schsession and a.classlvl=b.classlevel and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=?
left outer join tb_hse_common c on a.suid=c.suid and b.subjcode=c.code_id and c.tb_id='SBJ'
where a.schyear=? and b.sysscore>-1
order by a.classlvl, c.ch_des, b.omclasslvl

 

加插了分組名稱 , 試試以下的SQL

select
b.timeseq '考績',
a.classlvl,
a.classcode,
a.classno,
a.chname,
c.ch_des,
(case when d.SUBJGROUP is null then trim(cast(d.CROSSCLSGRP as char)+' '+cast(d.CROSSCLSSUBGRP as char)) else d.SUBJGROUP end) as '分組名稱',
b.sysscore '成績',
b.omclasslvl '排名'
from wsadmin.vw_stu_lateststudent a
left outer join wsadmin.tb_asr_subjassessdata b
on a.suid=b.suid and a.schlvl=b.schlevel and a.schsess=b.schsession and a.classlvl=b.classlevel
and a.schyear=b.schyear and a.stuid=b.stuid and b.timeseq=1101
left outer join wsadmin.tb_hse_common c on a.suid=c.suid and b.subjcode=c.code_id and c.tb_id='SBJ'
left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME d
on d.suid=b.suid and d.schlevel=b.schlevel and d.schsession=b.schsession and d.classlevel=b.classlevel
and d.schyear=b.schyear and d.stuid=b.stuid and d.timeseq=b.timeseq and d.subjcode=b.subjcode and d.SUBJCOMP is null
where a.schyear = ? and b.sysscore>-1
order by a.classlvl, c.ch_des, b.omclasslvl