简单SQL查询,求大侠指点
有表字段如下
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\


[解决办法]

SQL code

create table tb(a int,b varchar(100))insert into tbselect 1103 ,'\124\\126\' union allselect 1365 ,'\124\\475\\231\' union allselect 1303 ,'\136\\167\'goselect a.a,    '\'+substring(replace(replace(a.b,'\\',','),'\',''),b.number,                    charindex(',',replace(replace(a.b,'\\',','),'\','')+',',b.number)-b.number)+'\' bfrom tb a,master..spt_values bwhere 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 行受影响)