select soid,cus
into #soid
from (
select soid='1001',cus='Gavin'
union select soid='1002',cus='aduan'
union select soid='1003',cus='LH'
) a select orderNo,soid
into #order
from (
select orderNo='20090513',soid='1002/1003'
union select orderNo='20090514',soid='1001'
) b select orderNo,
soid = stuff((select '/'+ soid from (
select a.*,b.cus from
(
select orderNo,soid = substring(soid,number,charindex('/',soid+'/',number)-number)
from #order a ,
master..spt_values b
where b.type = 'P' and substring('/'+soid,number,1) = '/'
)a,#soid b
where a.soid = b.soid)t
where t.orderNo=a.orderNo for xml path('')), 1, 1, ''),
cus = stuff((select '/'+ cus from (
select a.*,b.cus from
(
select orderNo,soid = substring(soid,number,charindex('/',soid+'/',number)-number)
from #order a ,
master..spt_values b
where b.type = 'P' and substring('/'+soid,number,1) = '/'
)a,#soid b
where a.soid = b.soid)t
where t.orderNo=a.orderNo for xml path('')), 1, 1, '')from
(
select a.*,b.cus from
(
select orderNo,soid = substring(soid,number,charindex('/',soid+'/',number)-number)
from #order a ,
master..spt_values b
where b.type = 'P' and substring('/'+soid,number,1) = '/'
)a,#soid b
where a.soid = b.soid
)adrop table #soid,#order
--result
--orderNo soid cus
--20090513 1002/1003 aduan/LH
--20090514 1001 Gavin
into #soid
from (
select soid='1001',cus='Gavin'
union select soid='1002',cus='aduan'
union select soid='1003',cus='LH'
) a select orderNo,soid
into #order
from (
select orderNo='20090513',soid='1002/1003'
union select orderNo='20090514',soid='1001'
) b select orderNo,
soid = stuff((select '/'+ soid from (
select a.*,b.cus from
(
select orderNo,soid = substring(soid,number,charindex('/',soid+'/',number)-number)
from #order a ,
master..spt_values b
where b.type = 'P' and substring('/'+soid,number,1) = '/'
)a,#soid b
where a.soid = b.soid)t
where t.orderNo=a.orderNo for xml path('')), 1, 1, ''),
cus = stuff((select '/'+ cus from (
select a.*,b.cus from
(
select orderNo,soid = substring(soid,number,charindex('/',soid+'/',number)-number)
from #order a ,
master..spt_values b
where b.type = 'P' and substring('/'+soid,number,1) = '/'
)a,#soid b
where a.soid = b.soid)t
where t.orderNo=a.orderNo for xml path('')), 1, 1, '')from
(
select a.*,b.cus from
(
select orderNo,soid = substring(soid,number,charindex('/',soid+'/',number)-number)
from #order a ,
master..spt_values b
where b.type = 'P' and substring('/'+soid,number,1) = '/'
)a,#soid b
where a.soid = b.soid
)a
group by orderNo
drop table #soid,#order
--result
--orderNo soid cus
--20090513 1002/1003 aduan/LH
--20090514 1001 Gavin