SQL_SP:CrossTable(Rows change to Columns)

来源：互联网发布：崔晋张思然的淘宝店编辑：程序博客网时间：2024/05/22 02:14

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