求一表值函数
uid list tid
A |1|8|2| a|b|c
B |1|3|2| l|k|t
A |1|9|4| p|o|u
返回
a 1 a
a 1 b
a 1 c
a 8 a
a 8 b
a 8 c
b 1 l
b 1 k
b 1 t
[解决办法]
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'goselect 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)tidfrom tb a,master..spt_values b,master..spt_values cwhere 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 aA 8 aA 2 aA 1 bA 8 bA 2 bA 1 cA 8 cA 2 cB 1 lB 3 lB 2 lB 1 kB 3 kB 2 kB 1 tB 3 tB 2 tA 1 pA 9 pA 4 pA 1 oA 9 oA 4 oA 1 uA 9 uA 4 u(27 行受影响)*/godrop table tb