select convert(varchar(40),‘总计‘) as data_category11,* into #temp01 
from openquery(database,‘select line,sum(total_times) as total_times from table
where to_char(inserver_time-1,‘‘yyyy‘‘)=2019 group by line order by line‘)

insert into #temp01 
select convert(varchar(40),‘卡总计‘) as data_category,* 
from openquery(database,‘select line_no,sum(total_times) as total_times from table
where to_char(inserver_time-1,‘‘yyyy‘‘)=2019 and card_type=1 group by line_no order by line_no‘)

insert into #temp01 
select convert(varchar(40),‘卡总计‘) as data_category,* 
from openquery(database,‘select line_no,sum(total_times) as total_times from table
where to_char(inserver_time-1,‘‘yyyy‘‘)=2019 and card_type=1 group by line_no order by line_no‘)


select a.*,b.line_name into #temp_f from #temp01 a,gj.dbo.oo b where a.line_no=b.line_no

drop table #temp01


declare @line_name varchar(40)
set @line_name=‘%uuu%‘

select line_no,line_name,sum(客流) as ‘客流‘,sum(总计) as ‘卡总计‘,
sum(卡总计) as ‘卡总计‘ from
(
select line_no,line_name,case data_o when ‘总计‘ then total_times else 0 end as ‘客流‘, 
case data_category when ‘卡总计‘ then total_times else 0 end as ‘卡总计‘,
case data_category when ‘卡总计‘ then total_times else 0 end as ‘卡总计‘
from #temp_f where line_name like @line_name
)a group by line_no,line_name order by line_no,line_name



drop table #temp_f