with a(id,val)as( select 1,'aaa#2,bbb#2,ccc#4' union select 2,'bbb#3,ddd#5' union select 3,'ccc#3' union select 4,'aaa#5,ddd#3' ) select id ,substring(val,case when CHARINDEX('aaa',val)+4 =4 then 0 else CHARINDEX('aaa',val)+4 end,1)+0 aaa ,substring(val,case when CHARINDEX('bbb',val)+4 =4 then 0 else CHARINDEX('bbb',val)+4 end,1)+0 bbb ,substring(val,case when CHARINDEX('ccc',val)+4 =4 then 0 else CHARINDEX('ccc',val)+4 end,1)+0 ccc ,substring(val,case when CHARINDEX('ddd',val)+4 =4 then 0 else CHARINDEX('ddd',val)+4 end,1)+0 ddd from a
谢谢你的回答,但是val里的数值有可能是两位数,三位数的..你的aaa,bbb,ccc 是不是也是不确定的?嗯,不确定的! if exists(select 1 from sys.tables where name='test') drop table test create table test(id int,val varchar(100)) insert into test select 1,'aaa#2,bbb#2,ccc#4' union select 2,'bbb#3,ddd#5' union select 3,'ccc#3' union select 4,'aa#5,ddd#3'declare @sql varchar(max)select @sql=ISNULL(@sql,'')+',max(case when charindex('','+val+'#'','',''+val+''#'')>0 then substring(val,charindex('','',val+'','',charindex('''+val+''',val))-1,1) else 0 end) ''' +val+'''' from ( select distinct SUBSTRING(val+'#',number,CHARINDEX('#',val+'#',number)-number) val from test,master..spt_values where type='p' and SUBSTRING(','+val,number,1)=',')a exec( 'select id'+@sql+' from test group by id') 这样应该就行了
if exists(select 1 from sys.tables where name='test') drop table test create table test(id int,val varchar(100)) insert into test select 1,'aaa#2,bbb#2,ccc#4' union select 2,'bbb#3,ddd#5' union select 3,'ccc#3' union select 4,'aa#5,ddd#3' union select 5 'aa#12,bbb#123,ccc#22'declare @sql varchar(max)select @sql=ISNULL(@sql,'')+',max(case when charindex('','+val+'#'','',''+val+''#'')>0 then substring(val,charindex('','',val+'','',charindex('''+val+''',val))-1,1) else 0 end) ''' +val+'''' from ( select distinct SUBSTRING(val+'#',number,CHARINDEX('#',val+'#',number)-number) val from test,master..spt_values where type='p' and SUBSTRING(','+val,number,1)=',')a exec( 'select id'+@sql+' from test group by id') 大神你这里只能取到个位数的数据啊,再加一行 5 'aa#12,bbb#123,ccc#22' !就不行了
谢谢你的回答,但是val里的数值有可能是两位数,三位数的.. if exists(select 1 from sys.tables where name='test') drop table test create table test(id int,val varchar(100)) insert into test select 1,'aaa#2,bbb#2,ccc#4' union select 2,'bbb#3,ddd#5' union select 3,'ccc#3' union select 4,'aa#5,ddd#3' union select 5,'aa#12,bbb#123,ccc#22'declare @sql varchar(max)select @sql=ISNULL(@sql,'')+',max(case when charindex('','+val+'#'','',''+val+''#'')>0 then substring(val+'','',charindex(''#'',val,charindex('''+val+''',val))+1, charindex('','',val+'','',charindex('''+val+''',val)) -charindex(''#'',val,charindex('''+val+''',val))-1 ) else 0 end) ''' +val+'''' from ( select distinct SUBSTRING(val+'#',number,CHARINDEX('#',val+'#',number)-number) val from test,master..spt_values where type='p' and SUBSTRING(','+val,number,1)=',')a exec( 'select id'+@sql+' from test group by id')
with a(id,val)as(
select 1,'aaa#2,bbb#2,ccc#4' union
select 2,'bbb#3,ddd#5' union
select 3,'ccc#3' union
select 4,'aaa#5,ddd#3'
)
select id
,substring(val,case when CHARINDEX('aaa',val)+4 =4 then 0 else CHARINDEX('aaa',val)+4 end,1)+0 aaa
,substring(val,case when CHARINDEX('bbb',val)+4 =4 then 0 else CHARINDEX('bbb',val)+4 end,1)+0 bbb
,substring(val,case when CHARINDEX('ccc',val)+4 =4 then 0 else CHARINDEX('ccc',val)+4 end,1)+0 ccc
,substring(val,case when CHARINDEX('ddd',val)+4 =4 then 0 else CHARINDEX('ddd',val)+4 end,1)+0 ddd
from a
谢谢你的回答,但是val里的数值有可能是两位数,三位数的..
谢谢你的回答,但是val里的数值有可能是两位数,三位数的..你的aaa,bbb,ccc 是不是也是不确定的?
谢谢你的回答,但是val里的数值有可能是两位数,三位数的..你的aaa,bbb,ccc 是不是也是不确定的?嗯,不确定的!
谢谢你的回答,但是val里的数值有可能是两位数,三位数的..你的aaa,bbb,ccc 是不是也是不确定的?嗯,不确定的!
if exists(select 1 from sys.tables where name='test')
drop table test
create table test(id int,val varchar(100))
insert into test
select 1,'aaa#2,bbb#2,ccc#4' union
select 2,'bbb#3,ddd#5' union
select 3,'ccc#3' union
select 4,'aa#5,ddd#3'declare @sql varchar(max)select @sql=ISNULL(@sql,'')+',max(case when charindex('','+val+'#'','',''+val+''#'')>0
then substring(val,charindex('','',val+'','',charindex('''+val+''',val))-1,1) else 0 end) ''' +val+''''
from (
select distinct
SUBSTRING(val+'#',number,CHARINDEX('#',val+'#',number)-number) val
from test,master..spt_values where type='p'
and SUBSTRING(','+val,number,1)=',')a
exec( 'select id'+@sql+' from test group by id')
这样应该就行了
if exists(select 1 from sys.tables where name='test')
drop table test
create table test(id int,val varchar(100))
insert into test
select 1,'aaa#2,bbb#2,ccc#4' union
select 2,'bbb#3,ddd#5' union
select 3,'ccc#3' union
select 4,'aa#5,ddd#3' union
select 5 'aa#12,bbb#123,ccc#22'declare @sql varchar(max)select @sql=ISNULL(@sql,'')+',max(case when charindex('','+val+'#'','',''+val+''#'')>0
then substring(val,charindex('','',val+'','',charindex('''+val+''',val))-1,1) else 0 end) ''' +val+''''
from (
select distinct
SUBSTRING(val+'#',number,CHARINDEX('#',val+'#',number)-number) val
from test,master..spt_values where type='p'
and SUBSTRING(','+val,number,1)=',')a
exec( 'select id'+@sql+' from test group by id')
大神你这里只能取到个位数的数据啊,再加一行 5 'aa#12,bbb#123,ccc#22' !就不行了
谢谢你的回答,但是val里的数值有可能是两位数,三位数的..
if exists(select 1 from sys.tables where name='test')
drop table test
create table test(id int,val varchar(100))
insert into test
select 1,'aaa#2,bbb#2,ccc#4' union
select 2,'bbb#3,ddd#5' union
select 3,'ccc#3' union
select 4,'aa#5,ddd#3' union
select 5,'aa#12,bbb#123,ccc#22'declare @sql varchar(max)select @sql=ISNULL(@sql,'')+',max(case when charindex('','+val+'#'','',''+val+''#'')>0
then substring(val+'','',charindex(''#'',val,charindex('''+val+''',val))+1,
charindex('','',val+'','',charindex('''+val+''',val))
-charindex(''#'',val,charindex('''+val+''',val))-1
) else 0 end) ''' +val+''''
from (
select distinct
SUBSTRING(val+'#',number,CHARINDEX('#',val+'#',number)-number) val
from test,master..spt_values where type='p'
and SUBSTRING(','+val,number,1)=',')a
exec( 'select id'+@sql+' from test group by id')