CREATE PROCEDURE rowchgtocol AS
set nocount on
create table #tblA( [logrecid] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , BH char(3) not null, GZLB varchar(3) not null, JE int, )
create table #tmp(bh char(3),a int null,b int null,c int null,d int null,e int null)
insert #tblA values('002', 'A', 100)
insert #tblA values('002', 'B', null)
insert #tblA values('002', 'C', 110)
insert #tblA values('002', 'D', 150)
insert #tblA values('002', 'E', 110)
insert #tblA values('001', 'A', 99)
insert #tblA values('001', 'B', null)
insert #tblA values('001', 'C', 150)
insert #tblA values('001', 'D', 150)
insert #tblA values('001', 'E', 110)
insert #tblA values('003', 'A', null)
insert #tblA values('003', 'B', 10)
insert #tblA values('003', 'C', null)
insert #tblA values('003', 'D', null)
insert #tblA values('003', 'E', 110)
declare @sql nvarchar(4000)
set @sql = 'insert into #tmp select * from (select bh, '
--select @sql = @sql +' sum(case GZLB when '''+ GZLB +''' then (case JE when isnull(JE,0)<>0 then JE else 0 end) else 0 end) ['+ GZLB +'],' from (select distinct GZLB as GZLB from tblA) tmp
select @sql = @sql +' sum(case GZLB when '''+ GZLB +''' then JE else null end) ['+ GZLB +'],' from (select distinct GZLB as GZLB from #tblA) tmp set @sql = substring(@sql, 1, len(@sql)-1) +' from #tblA group by BH) as a'
exec sp_executesql @sql
select * from #tmp