單純、幸福

Select 4 stom_id,'去冰,半糖' od_memo Into #D union all
Select 9 stom_id,'' od_memo union all
Select 5 stom_id,'' od_memo union all
Select 6 stom_id,'' od_memo union all
Select 5 stom_id,'無糖,去冰' od_memo union all
Select 6 stom_id,'半糖' od_memo union all
Select 4 stom_id,'無糖' od_memo ;

Select 4 stom_id,N'茉莉綠茶(大)' stom_Item Into #T Union All
Select 5 stom_id,N'茉莉綠茶(中)' stom_Item Union All
Select 6 stom_id,N'阿薩姆紅茶(大)' stom_Item Union All
Select 9 stom_id,N'珍珠奶茶(中)' stom_Item 

--彙整特製品數量
select d.stom_id,od_memo+'('+Convert(varchar,Count(1))+')' as od_memo
Into #SubOD
From #D as d
where od_memo<>''
group by d.stom_id,od_memo



select a.*,t.stom_Item
--字串處理去除結尾'/'字符
,Case When len(b.od_memo)>0 then  substring(b.od_memo,1,len(b.od_memo)-1) else '' end as od_memo 
From 
(
	select d.stom_id,Count(1) as total
	from #D as d 
	group by d.stom_id
)as a
left join 
--特製品
(
	select stom_id,
	(
		--巢狀子查詢帶入XML字串結合
		Select od_memo+'/'
		From #SubOD as a
		where a.stom_id=p.stom_id
		FOR XML PATH('') 
	)as od_memo
	From #SubOD as p
	Group By p.stom_id	
)As b on a.stom_id=b.stom_id
--品名關連
left join #T as t on a.stom_id=t.stom_id