DECLARE @SQL VARCHAR(1000)
set @sql=''
select @sql=@sql+',sum(case when Color='''+Color+'''
then aCount else 0 end) ' + Color
from (select distinct Color from WuZi_Table a ,YanSe_Table b where a.color_id=b.color_id) aa
exec ('select id '+@sql+ ' from WuZi_Table a ,YanSe_Table b where a.color_id=b.color_id')
SELECT ID,SUM(CASE WHEN Color='红色' THEN aCount ELSE 0 END) AS 红色,
SUM(CASE WHEN TYPE='兰色' THEN aCount ELSE 0 END) AS 兰色,
SUM(CASE WHEN TYPE='绿色 ' THEN aCount ELSE 0 END) AS 绿色
from WuZi_Table a ,YanSe_Table b where a.color_id=b.color_id
GROUP BY ID
set @sql='SELECT '
select @sql=@sql+',sum(case when Color='''+Color+'''
then aCount else 0 end) ' + Color
from (select distinct Color from WuZi_Table a ,YanSe_Table b where a.color_id=b.color_id) aa
select @sql=@sql+' from WuZi_Table group by id 'exec(@sql)