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...)
---------------------------------------------------------------
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) ----是其中的-----
/*****************************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)**********************************/
能说的详细点吗,小弟实在看不懂他为什么这么做!!!!!!!!!!!!!!!!!!!
----------------------
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...)
---------------------------------------------------------------
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) ----是其中的-----
/*****************************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)**********************************/
能说的详细点吗,小弟实在看不懂他为什么这么做!!!!!!!!!!!!!!!!!!!
/*****************************
--定义一个变量,用来存放SQL执行语句
declare @sql varchar(600)
--赋初值
set @sql = 'select'
--过滤重复记录并写到临时表tmp里
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
--去掉@Sql最后面的一个",",否则会给错误提示
set @sql = substring(@sql, 1, len(@sql)-1) +' from tblA group by BH'
----你可以加上下边这句,输入动态生的语句
print @sql
--执行动态语句
exec(@sql)**********************************/
set @sql='select BH'
select @sql=@sql+',['+GZLB+']=max(case GZLB when '''+ GZLB +''' then JE else null end)' from tblA group by GZLB
exec(@sql+ ' from tblA group by BH' )
PIVOT
(
max(JE)
for GZLB in ([A],[B],[C])
) as PVT