如表中有A、B、C三个字段,数据如下:
A B C
1,2 3 4
要求查询结果:
A B C
1 3 4
2 3 4
查询语句:
with t as
(
select B,C
,A=case charindex(',',A) when 0 then A else left(A,charindex(',',A)-1) end
,Aleft=case charindex(',',A) when 0 then '' else substring(A,charindex(',',A)+1,len(A)) end
from 表名
WHERE 1=1 --条件,看个人需求
union all
select B,C
,A=case charindex(',',Aleft) when 0 then Aleft else left(Aleft,charindex(',',Aleft)-1) end
,Aleft=case charindex(',',Aleft) when 0 then '' else substring(Aleft,charindex(',',Aleft)+1,len(Aleft)) end
from t
where Aleft <> ''
)
select A,B,C from t