declare @sql varchar(8000);
select distinct Repp
into #repp
from v_datayearall;
select a_code,i_code,repp,a_name,i_name+case when t_indct.m_code is not null then '('+s_name+')' else '' end as i_name
into #tempType
from t_INDCT,t_area,#repp,t_Scale
where t_INDCT.i_code in ('1','2','3','4','5','6','7')
and t_area.a_code in ('420321102000','420321102200','420321102201','420321102202','420321102203','420321102204')
and repp in ('2005')
and t_INDCT.m_code = t_Scale.m_code
and scale = 0;
declare @exp money;
set @exp=10;
select #tempType.*,ISNULL(convert(nvarchar,convert(decimal(30,3),case when Data is not null and scale is not null then Data*power(@exp,scale) else Data end)),text_data) data
--into #tempMain
from v_datayearall ,#tempType
where v_datayearall.i_code =* #tempType.i_code
and v_datayearall.a_code =* #tempType.a_code
and v_datayearall.repp =* #tempType.repp;
select distinct a_name as a_name,a_code
into #temptin
from #tempMain
order by a_code;
set @sql = 'select i_name as 指标';
select @sql = @sql + ',max(case a_name
when '''+a_name+''' then data end) ['+a_name+']'
from #temptin;set @sql = @sql+' from #tempMain group by i_code,i_name order by i_code';
exec(@sql);
drop table #repp;
drop table #tempType;
drop table #tempMain;
drop table #temptin;