存储过程

create procedure proctemp_temp   
 @sql varchar(8000) 
AS

BEGIN
 SET NOCOUNT ON --当 SET NOCOUNT 为 ON 时，不返回计数
 if   object_id('tempdb..#TEMP_TEMP')   is   not   null  
    drop   table   #TEMP_TEMP 
 CREATE TABLE #TEMP_TEMP
 (
    ID int IDENTITY(1,1),
    NAME VARCHAR(100),
    ORG VARCHAR(100),
    TRADE VARCHAR(200),
    f0170 VARCHAR(50),
       f0130 VARCHAR(50)
 )
truncate table #temp_temp

 --set @sql='insert into #TEMP_TEMP(trade,name,org,f0170,f0130)select f0160,name,f0190,f0170,f0130 from tb_1130 where f0140=2008 and f0150=''行业'' and f0170<=''3'' order by f0160,f0170'
 exec(@sql)--传入插入临时表的sql 语句

--创建临时表2 用于从临时表1获取满足条件的临时表1的数据
if   object_id('tempdb..#TEMP')   is   not   null  
    drop   table   #TEMP 
 CREATE TABLE #TEMP
 (
    ID int IDENTITY(1,1),
    NAME VARCHAR(50),
    F0190 VARCHAR(50),
    F0160 VARCHAR(200),
    F01701 VARCHAR(100),
    F01702 VARCHAR(100),
    F01703 VARCHAR(100),

    F01704 VARCHAR(100),
    F01705 VARCHAR(100),
    F01706 VARCHAR(100),

    F01707 VARCHAR(100),
    F01708 VARCHAR(100),
    F01709 VARCHAR(100),

    KL1    VARCHAR(2),
    KL2    VARCHAR(2),
    KL3    VARCHAR(2),

    KL4    VARCHAR(2),
    KL5    VARCHAR(2),
    KL6    VARCHAR(2),

    KL7    VARCHAR(2),
    KL8    VARCHAR(2),
    KL9    VARCHAR(2)
 )
--    insert into #TEMP(f01701,kl1)
--    select name,org,trade,f0170,f0130 from #TEMP_TEMP a where f0170='1' and id=(select min(id) from #TEMP_TEMP where trade=a.trade)

  
--truncate table #temp--删除临时表2的数据
insert into #TEMP(f0160)--像临时表2插入数据
select distinct trade from #temp_temp

declare @number int   --用于记录循环的次数
set @number=1
while @number<4--修改临时表2的数据
begin
if (@number=1)
 begin
update a set F01701=(select name+'/'+org from #TEMP_TEMP where f0170='1' and trade=a.f0160 and id=(select min(id) from #TEMP_TEMP where trade=a.f0160 and f0170='1') )
,KL1 = (select f0130 from #TEMP_TEMP b where trade=a.f0160 and f0170='1' and id =
(select min(id) from #TEMP_TEMP b where trade=a.f0160 and f0170='1'))
from #TEMP a
delete from #TEMP_TEMP where id in (select id from #TEMP_TEMP a
where f0170='1' and id=(select min(id) from #TEMP_TEMP where trade=a.trade and f0170='1'))

update a set F01702=(select name+'/'+org from #TEMP_TEMP where f0170='2' and trade=a.f0160 and id=(select min(id) from #TEMP_TEMP where trade=a.f0160 and f0170='2') )
,KL2 = (select f0130 from #TEMP_TEMP b where trade=a.f0160 and f0170='2' and id =
(select min(id) from #TEMP_TEMP b where trade=a.f0160 and f0170='2'))
from #TEMP a

delete from #TEMP_TEMP where id in (select id from #TEMP_TEMP a
where f0170='2' and id=(select min(id) from #TEMP_TEMP where trade=a.trade and f0170='2'))

update a set F01703=(select name+'/'+org from #TEMP_TEMP where f0170='3' and trade=a.f0160 and id=(select min(id) from #TEMP_TEMP where trade=a.f0160 and f0170='3') )
,KL3 = (select f0130 from #TEMP_TEMP b where trade=a.f0160 and f0170='3' and id =
(select min(id) from #TEMP_TEMP b where trade=a.f0160 and f0170='3'))
from #TEMP a

delete from #TEMP_TEMP where id in (select id from #TEMP_TEMP a
where f0170='3' and id=(select min(id) from #TEMP_TEMP where trade=a.trade and f0170='3'))
end

if (@number=2)
 begin
update a set F01704=(select name+'/'+org from #TEMP_TEMP where f0170='1' and trade=a.f0160 and id=(select min(id) from #TEMP_TEMP where trade=a.f0160 and f0170='1') )
,KL4 = (select f0130 from #TEMP_TEMP b where trade=a.f0160 and f0170='1' and id =
(select min(id) from #TEMP_TEMP b where trade=a.f0160 and f0170='1'))
from #TEMP a

delete from #TEMP_TEMP where id in (select id from #TEMP_TEMP a
where f0170='1' and id=(select min(id) from #TEMP_TEMP where trade=a.trade and f0170='1'))

update a set F01705=(select name+'/'+org from #TEMP_TEMP where f0170='2' and trade=a.f0160 and id=(select min(id) from #TEMP_TEMP where trade=a.f0160 and f0170='2') )
,KL5 = (select f0130 from #TEMP_TEMP b where trade=a.f0160 and f0170='2' and id =
(select min(id) from #TEMP_TEMP b where trade=a.f0160 and f0170='2'))
from #TEMP a

delete from #TEMP_TEMP where id in (select id from #TEMP_TEMP a
where f0170='2' and id=(select min(id) from #TEMP_TEMP where trade=a.trade and f0170='2'))

update a set F01706=(select name+'/'+org from #TEMP_TEMP where f0170='3' and trade=a.f0160 and id=(select min(id) from #TEMP_TEMP where trade=a.f0160 and f0170='3') )
,KL6 = (select f0130 from #TEMP_TEMP b where trade=a.f0160 and f0170='3' and id =
(select min(id) from #TEMP_TEMP b where trade=a.f0160 and f0170='3'))
from #TEMP a

delete from #TEMP_TEMP where id in (select id from #TEMP_TEMP a
where f0170='3' and id=(select min(id) from #TEMP_TEMP where trade=a.trade and f0170='3'))
end

if (@number=3)
 begin
update a set F01707=(select name+'/'+org from #TEMP_TEMP where f0170='1' and trade=a.f0160 and id=(select min(id) from #TEMP_TEMP where trade=a.f0160 and f0170='1') )
,KL7 = (select f0130 from #TEMP_TEMP b where trade=a.f0160 and f0170='1' and id =
(select min(id) from #TEMP_TEMP b where trade=a.f0160 and f0170='1'))
from #TEMP a

delete from #TEMP_TEMP where id in (select id from #TEMP_TEMP a
where f0170='1' and id=(select min(id) from #TEMP_TEMP where trade=a.trade and f0170='1'))

update a set F01708=(select name+'/'+org from #TEMP_TEMP where f0170='2' and trade=a.f0160 and id=(select min(id) from #TEMP_TEMP where trade=a.f0160 and f0170='2') )
,KL8 = (select f0130 from #TEMP_TEMP b where trade=a.f0160 and f0170='2' and id =
(select min(id) from #TEMP_TEMP b where trade=a.f0160 and f0170='2'))
from #TEMP a

delete from #TEMP_TEMP where id in (select id from #TEMP_TEMP a
where f0170='2' and id=(select min(id) from #TEMP_TEMP where trade=a.trade and f0170='2'))

update a set F01709=(select name+'/'+org from #TEMP_TEMP where f0170='3' and trade=a.f0160 and id=(select min(id) from #TEMP_TEMP where trade=a.f0160 and f0170='3') )
,KL9 = (select f0130 from #TEMP_TEMP b where trade=a.f0160 and f0170='3' and id =
(select min(id) from #TEMP_TEMP b where trade=a.f0160 and f0170='3'))
from #TEMP a

delete from #TEMP_TEMP where id in (select id from #TEMP_TEMP a
where f0170='3' and id=(select min(id) from #TEMP_TEMP where trade=a.trade and f0170='3' ))
end
set @number = @number+1
End

select * from #TEMP
End

--调用

exec proctemp_temp  ''insert into #TEMP_TEMP(trade,name,org,f0170,f0130)select f0160,name,f0190,f0170,f0130 from tb_1130 where f0140=2008 and f0150=''行业'' and f0170<=''3'' order by f0160,f0170'