create table tb(uid varchar(10),list varchar(10),tid varchar(10))
insert into tb select 'A','|1|8|2|','a|b|c'
insert into tb select 'B','|1|3|2|','l|k|t'
insert into tb select 'A','|1|9|4|','p|o|u'
go
select a.uid,substring(a.list,b.number,charindex('|',a.list,b.number+1)-b.number)list,
substring(a.tid,c.number,charindex('|',a.tid+'|',c.number+1)-c.number)tid
from tb a,master..spt_values b,master..spt_values c
where b.type='p' and b.number<=len(a.list) and substring(a.list,b.number,1)<>'|' and substring('|'+a.list,b.number,1)='|'
and c.type='p' and c.number<=len(a.tid) and substring(a.tid,c.number,1)<>'|' and substring('|'+a.tid,c.number,1)='|'
/*
uid        list       tid
---------- ---------- ----------
A          1          a
A          8          a
A          2          a
A          1          b
A          8          b
A          2          b
A          1          c
A          8          c
A          2          c
B          1          l
B          3          l
B          2          l
B          1          k
B          3          k
B          2          k
B          1          t
B          3          t
B          2          t
A          1          p
A          9          p
A          4          p
A          1          o
A          9          o
A          4          o
A          1          u
A          9          u
A          4          u

(27 行受影响)

*/
go
drop table tb