SQL语句实现横排？

[foxconn80]

请教各路高手？
有一个表：
BH GZLB JE
----------------------
001 A 100
001 B 150
001 C 110
002 A 99
002 B 180
002 C 150
003 A 160
003 B 170
003 C 130
用SQL语句如何将上面的表变成下面横向的排列方式呢。
BH A B C
-------------------
001 100 150 110
002 99 180 150
003 160 170 130
（GZLB 里面的字段是动态的，不是只有A,B,C 可能还有A,B,C,D,E...）

[HawaiiLeo]

if exists(select name from sysobjects where name='tblA' and xtype='U')
drop table tblA

create table tblA(
BH char(3) not null,
GZLB varchar(3) not null,
JE int not null
)
go

insert tblA values('001', 'A', 100)
insert tblA values('001', 'B', 150)
insert tblA values('001', 'C', 110)
insert tblA values('002', 'A', 99)
insert tblA values('002', 'B', 180)
insert tblA values('002', 'C', 150)
insert tblA values('003', 'A', 160)
insert tblA values('003', 'B', 170)
insert tblA values('003', 'C', 130)

declare @sql varchar(600)
set @sql = 'select'
select distinct GZLB as 'GZLB' into tmp from tblA


select @sql = @sql +' max(case GZLB when '''+ GZLB +''' then JE else null end) as '''+ GZLB +''',' from tmp
set @sql = substring(@sql, 1, len(@sql)-1) +' from tblA group by BH'
exec(@sql)

[HawaiiLeo]

if exists(select name from sysobjects where name='tblA' and xtype='U')
drop table tblA
if exists(select name from sysobjects where name='tmp' and xtype='U')
drop table tmp

create table tblA(
BH char(3) not null,
GZLB varchar(3) not null,
JE int not null
)
go

insert tblA values('001', 'A', 100)
insert tblA values('001', 'B', 150)
insert tblA values('001', 'C', 110)
insert tblA values('002', 'A', 99)
insert tblA values('002', 'B', 180)
insert tblA values('002', 'C', 150)
insert tblA values('003', 'A', 160)
insert tblA values('003', 'B', 170)
insert tblA values('003', 'C', 130)

declare @sql varchar(600)
set @sql = 'select'
select distinct GZLB as 'GZLB' into tmp from tblA


select @sql = @sql +' max(case GZLB when '''+ GZLB +''' then JE else null end) as '''+ GZLB +''',' from tmp
set @sql = substring(@sql, 1, len(@sql)-1) +' from tblA group by BH'
exec(@sql)

[jsyhello]

动态的好，强烈支持 大力
不知数据量很大时，它的执行效率如何？
以前，我是通过delphi的表格，实现数据横排的，解决了开发程序通用查询问题

[snowfox_yy]

用游标
declare @sqlstr varchar(2000)
declare @gzlb
select sqlstr='select bh'
declare cgzlb cursor for select gzlb from table
create table #a
(bh char(3))
open cgzlb
fetch next from cgzlb into @gzlb
while (fetch_status<>-1)
begin
exec('alter table #a add '+@gzlb +' char(1)')
exec('insert into #a (bh,'+@gzlb+' select bh,je from table where gzlb='''+@gzlb+'''')
select @sqlstr=@sqlstr+',sum(isnull('+@gzlb+'))'


fetch next from cgzlb into @gzlb
end
close cgzlb
deallocate cgzlb
select @sqlstr=@sqlstr+' from #a group by bh order by bh
execute(@sqlstr)

[snowfox_yy]

上面有一点错误
用游标
declare @sqlstr varchar(2000)
declare @gzlb
select sqlstr='select bh'
declare cgzlb cursor for select gzlb from table
create table #a
(bh char(3))
open cgzlb
fetch next from cgzlb into @gzlb
while (fetch_status<>-1)
begin
exec('alter table #a add '+@gzlb +' char(1)')
exec('insert into #a (bh,'+@gzlb+' select bh,je from table where gzlb='''+@gzlb+'''')
select @sqlstr=@sqlstr+',sum(isnull('+@gzlb+',0))'


fetch next from cgzlb into @gzlb
end
close cgzlb
deallocate cgzlb
select @sqlstr=@sqlstr+' from #a group by bh order by bh
execute(@sqlstr)

[pengdali]

declare @sql varchar(6000)
set @sql = 'select '
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'
exec(@sql)


---------------------
你把工资类别写死了。能不能写活一点呢

re: 你仔细看看有没有写死？

[jsyhello]

select bh,
sum(case when GzLB='A' then JE else 0 end) as A,
sum(case when GzLB='B' then JE else 0 end) as B,
sum(case when GzLB='C' then JE else 0 end) as C
from 表名
group by bh

[yoki]

这个是动态的呀
declare @sql varchar(6000)
set @sql = 'select '
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'
exec(@sql)

[guanhua8]

强烈支持（大力），你的基础不够哦

[zsforever]

declare @sql varchar(6000)
set @sql = 'select bh, '//不同
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'
exec(@sql)
大力的第二行这么写可以得到编号列

[foxconn80]

感谢各路高手指点，此问题我已经解决了。

[zsforever]

select bh, sum(case GZLB when 'a' then JE else null end)[a],sum(case GZLB when 'b' then JE else null end)[b],sum(case GZLB when 'c' then JE else null end)[c] from table_test group by bh

这是个静态的，大力的代码前面也就是为了得到这些变量而设的！
强烈支持 大力 。

[foxconn80]

你把工资类别写死了。能不能写活一点呢。