多筆相同鍵值的欄位內容合併加陣列欄位查詢

-- 建立模擬資料
if exists(select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.#temp') )
drop table #temp

create table #temp (
sno varchar(10), --學號
class varchar(10), --科目
num int --分數
)
insert into #temp
select '001', '國', 80
union select '001', '英', 90
union select '001', '數', 55
union select '002', '國', 50
union select '002', '英', 60
union select '002', '數', 75
union select '003', '英', 98
union select '003', '數', 59
union select '004', '國', 88
union select '004', '英', 98
union select '005', '國', 58
union select '005', '數', 69
union select '006', '國', 78
union select '006', '英', 84
union select '006', '數', 59

print '資料表列'
select * from #temp

-- 多筆相同鍵值的欄位內容合併 
select a.sno, (
stuff((
select ','+class+' '+convert(varchar(10),num )
from #temp b
where a.sno=b.sno
for xml path('')
), 1,1,'')
) class
from #temp a
group by a.sno

select distinct a.sno, (
stuff((
select ','+class+' '+convert(varchar(10),num )
from #temp b
where a.sno=b.sno
for xml path('')
), 1,1,'')
) class
from #temp a

-- 陣列欄位查詢方式
if exists(select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.#t2') )
drop table #t2

create table #t2 (
pName varchar(10), --條件名稱
numAry varchar(10) --分數條件陣列
)
insert into #t2
select 'p1', '80'
union select 'p2', '80,90,100'
union select 'p3', '50,58,98'

select * from #t2
select * from #temp

-- 測試查詢 使用 charindex <<正常>> ; 但 changeClass 只能為 1 筆, 否則異常
select * from #temp t
where charindex(cast(num as varchar),(select top 1 numAry from #t2 ))>0

-- 測試查詢 使用 charindex + for xml path('') <<正常>> ; 但 changeClass 可以為多筆
select * from #temp t
where charindex(cast(num as varchar),(
stuff((
select ','+numAry
from #t2
where 1=1 -- 可設定條件
and pName in ('p3')
for xml path('')
), 1,1,'')
))>0

/*
-- 查詢結果
sno class num
002 國 50
003 英 98
004 英 98
005 國 58

*/