表A两列A1, A2,数据显示如下
A1 A2
000 ('A001','A002','A003')
怎么换换成这样显示:
000 A001
000 A002
000 A003
------解决思路----------------------
with A(A1,A2) as
(select '000','(''001'',''002'',''003'')'),
B as
(select A1,A2=substring(REPLACE(A2,'''',''),2,LEN(REPLACE(A2,'''',''))-2) from A)
select A1,SUBSTRING(A2,number,charindex(',',A2+',',number)-number) as A2 from B,master..spt_values
where type='P' and number>=1 and number<=LEN(A2) and SUBSTRING(','+A2,number,1)=','