简单SQL查询,求大侠指点

wjf923 2012-02-24 02:34:31

有表字段如下
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\

...全文

56 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

wjf923 2012-02-24

打赏
举报

谢谢啊搞定

AcHerat 元老 2012-02-24

打赏
举报



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