create table T(名称 varchar(10),颜色 varchar(10),数量 int)
insert T select 'a','aa', 20
union all select 'a','cc', 10
union all select 'b','dd', 5
union all select 'b','bb', 25
union all select 'c','aa', 30
select * from T
select 名称,
sum(isnull(case when 颜色='aa' then 数量 end,0)) aa,
sum(isnull(case when 颜色='bb' then 数量 end,0)) bb,
sum(isnull(case when 颜色='cc' then 数量 end,0)) cc,
sum(isnull(case when 颜色='dd' then 数量 end,0)) dd,
sum(数量) 总数
from T group by 名称