declare @string varchar(1000)
set @string='select identity(int,1,1) as id,* into ##tmp from (select '''+replace(@id,',','''as purc_need_id union all select ''')+''') a'
exec(@string)
select a.prod_id,b.purc_need_id,a.supp_id into #aaa
from purchase_prod a,purchase b
where a.purc_need_id=b.purc_need_id and b.purc_need_id
in (select c.purc_need_id from ##tmp c,purchase d where c.purc_need_id=d.purc_need_id ) order by prod_id
select a.prod_id,a.prod_name,a.prod_type,a.purc_proc_quan,a.purc_proc_total_price, 0 quan, cast('' as varchar(500)) as value into #ccc
from purchase_prod a,#aaa b
where a.prod_id=b.prod_id and a.purc_need_id=b.purc_need_id and a.supp_id=b.supp_id
--select * from #aaa
select prod_id,prod_name,prod_type,sum(purc_proc_quan) as quan,sum(purc_proc_total_price) as total from #ccc group by prod_id,prod_name,prod_type
--select max(prod_id) as prod_id,max(value) result into #ddd from #ccc group by quan
--select * from #ccc
--select * from #ccc order by prod_id
--select sum(purc_proc_quan) as quan1 ,sum(purc_proc_total_price) as total from #ccc where prod_id=21
drop table ##tmp
drop table #aaa
drop table #ccc