34,575
社区成员
表temp1格式如下
code remark1 remark2
123 A1,A2,A3,A4,A5 B1,B2,B3,B4,B5
希望查询结果如下
code remark1 remark2
123 A1 B1
123 A2 B2
123 A3 B3
123 A4 B4
123 A5 B5
;with t as
(
select code = '123',remack1 = 'a1,a2,a3',remack2 = 'b1,b2,b3'
)
,t2 as
(
select code,k1 = ','+remack1,k2=','+remack2
from t
)
,t3 as
(
select code,v.number,text1 = substring(t2.k1,v.number,len(t2.k1)),text2 = substring(t2.k2,v.number,len(t2.k2))
from t2,master.dbo.spt_values v
where v.type = 'P'
and v.number between 1 and len(t2.k1)
and substring(t2.k1,v.number,1)=','
and substring(t2.k2,v.number,1)=','
)
,t4 as
(
select *,k11 = charindex(',',t3.text1,2)
,k22 = charindex(',',t3.text2,2)
from t3
)
select code,number,text1
,newtext1 = case when k11>0 then substring(text1,2,k11-2) else substring(text1,2,len(text1)) end
,text2
,newtext2 = case when k22>0 then substring(text2,2,k22-2) else substring(text2,2,len(text2)) end
from t4
select a.other_cols,
col_split=substring(a.col_split, b.number, charindex('|', a.col_split + '|', b.number) - b.number)
from [TableName] a
join master..spt_values b on b.type='P'
where charindex('|', '|' + a.col_split, b.number) = b.number
没有想到更好的方式,只有这种笨办法了
--创建split函数
CREATE function [dbo].[Split]
(
@SourceSql varchar(max),
@StrSeprate varchar(10)
)
returns @temp table(value varchar(200))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
--创建示例表
create table temp1 (code int,remark1 nvarchar(max),remark2 nvarchar(max))
--写入示例数据
insert into temp1 values(1,'A1,A2,A3,A4,A5','B1,B2,B3,B4,B5') --正常例子
,(2,'A1,A2,A3,A4,A5,A6','B1,B2,B3,B4,B5') --remark1多于remark2时
,(3,'A1,A2,A3,A4,A5','B1,B2,B3,B4,B5,B6') --remark2多于remark1时
,(4,'A5,A4,A3,A2,A1','B5,B4,B3,B2,B1') --倒序字符串
--查询
select
code = isnull(t1.code,t2.code)
,t1.remark1
,t2.remark2
from (
select temp1.code,remark1 = t1.value,row =ROW_NUMBER()over(partition by temp1.code order by (select 1)) from temp1
outer apply(select value from Split(temp1.remark1,','))t1
)t1
full join (
select temp1.code,remark2 = t2.value,row =ROW_NUMBER()over(partition by temp1.code order by (select 1)) from temp1
outer apply(select value from Split(temp1.remark2,','))t2
) t2 on t2.code = t1.code and t2.row = t1.row
order by code