create table A (d char(8),
sAcount int,
pAcount int,
gAcount int
)
insert into a values('04-12-6 ',100,50,50)
insert into a values('04-12-8 ',100,70,130)
insert into a values('04-12-9 ',98,28,70)
insert into a values('04-12-12',110,60,50)
declare @s1 varchar(2000),@s2 varchar(2000),@s3 varchar(2000),@s4 varchar(2000),@s varchar(8000)
set @s1='select ''sAcount'''
select @s1=@s1+ ',['+rtrim(d)+']=max(case d when''' + rtrim(d) + ''' then sAcount else null end)' from (select distinct d from a )b
set @s1= @s1 + ' from
(select d,sum(sAcount) as sAcount from a
group by d)c'
set @s2='select ''pAcount'''
select @s2=@s2+ ',['+rtrim(d)+']=max(case d when''' + rtrim(d) + ''' then pAcount else null end)' from (select distinct d from a )b
set @s2= @s2 + ' from
(select d,sum(pAcount) as pAcount from a
group by d)c'
set @s3='select ''gAcount'''
select @s3=@s3+ ',['+rtrim(d)+']=max(case d when''' + rtrim(d) + ''' then gAcount else null end)' from (select distinct d from a )b
set @s3= @s3 + ' from
(select d,sum(gAcount) as gAcount from a
group by d)c'
exec(@s1+ ' union all '+ @s2 + ' union all '+ @s3)