22,209
社区成员
发帖
与我相关
我的任务
分享
表数据
id val
1 aaa#2,bbb#2,ccc#4
2 bbb#3,ddd#5
3 ccc#3
4 aaa#5,ddd#3
想得到数据
id aaa bbb ccc ddd
1 2 2 4 0
2 0 3 0 5
3 0 0 3 0
4 5 0 0 3
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' !就不行了
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')
这样应该就行了
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
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')