简单SQL查询,求大侠指点

Question

有表字段如下  
StudiesID    StudiesExam
 1103         \124\\126\
 1365         \124\\475\\231\
 1303         \136\\167\
想转化后得到新表
StudiesID    StudiesExam
1103         \124\
1103         \126\
1365         \124\
1365         \475\
1365         \231\
1303         \136\
1303         \167\

Answer 1

create table tb(a int,b varchar(100))
insert into tb
select 1103 ,'\124\\126\' union all
select 1365 ,'\124\\475\\231\' union all
select 1303 ,'\136\\167\'
goselect a.a,
'\'+substring(replace(replace(a.b,'\\',','),'\',''),b.number,
charindex(',',replace(replace(a.b,'\\',','),'\','')+',',b.number)-b.number)+'\' b
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(replace(replace(a.b,'\\',','),'\',''))
and substring(','+replace(replace(a.b,'\\',','),'\',''),b.number,1) = ','drop table tb/***********************************a           b
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1103        \124\
1103        \126\
1365        \124\
1365        \475\
1365        \231\
1303        \136\
1303        \167\(7 行受影响)