求一SQL查询

[斗尧]

表数据      
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

求各位大神帮忙

[哥眼神纯洁不]

引用 2 楼 bf6688 的回复:

[quote=引用 1 楼 sc273607742 的回复:]

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里的数值有可能是两位数，三位数的..[/quote]

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

[斗尧]

引用 1 楼 sc273607742 的回复:

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里的数值有可能是两位数，三位数的..

[哥眼神纯洁不]

引用 2 楼 bf6688 的回复:

[quote=引用 1 楼 sc273607742 的回复:]

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里的数值有可能是两位数，三位数的..[/quote] 你的aaa，bbb，ccc 是不是也是不确定的?

[斗尧]

引用 3 楼 sc273607742 的回复:

[quote=引用 2 楼 bf6688 的回复:] [quote=引用 1 楼 sc273607742 的回复:]

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里的数值有可能是两位数，三位数的..[/quote] 你的aaa，bbb，ccc 是不是也是不确定的?[/quote] 嗯，不确定的！

[哥眼神纯洁不]

引用 4 楼 bf6688 的回复:

[quote=引用 3 楼 sc273607742 的回复:] [quote=引用 2 楼 bf6688 的回复:] [quote=引用 1 楼 sc273607742 的回复:]

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里的数值有可能是两位数，三位数的..[/quote] 你的aaa，bbb，ccc 是不是也是不确定的?[/quote] 嗯，不确定的！[/quote]

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')

这样应该就行了

[斗尧]

引用 5 楼 sc273607742 的回复:

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' ！就不行了