日期:2014-05-18  浏览次数:20571 次

简单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 tb
select 1103 ,'\124\\126\' union all
select 1365 ,'\124\\475\\231\' union all
select 1303 ,'\136\\167\'
go

select 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 行受影响)