求1SQL查询

求一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

求各位大神帮忙
------解决方案--------------------

引用:

Quote: 引用:

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

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