3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
怎么形成这样的二维表
原来的表：
WLBH DWBH SL JE
WL01 DW01 2 10
WL02 DW02 3 11
WL03 DW03 4 12
利用存储过程或者ＳＱＬ变成：
DWBH WL01_SL WL01_JE WL02_SL WL02_JE WL03_SL WL03_JE
DW01 2 10
DW02 3 11
DW03 4 12
WLBH DWBH SL JE
WL01 DW01 2 10
WL02 DW02 3 11
WL03 DW03 4 12
利用存储过程或者ＳＱＬ变成：
DWBH WL01_SL WL01_JE WL02_SL WL02_JE WL03_SL WL03_JE
DW01 2 10
DW02 3 11
DW03 4 12
...全文
请发表友善的回复…
发表回复
子陌红尘 2006-01-24
create table t(WLBH varchar(10),DWBH varchar(10),SL int,JE int)
insert into t select 'WL01','DW01',2,10
insert into t select 'WL02','DW02',3,11
insert into t select 'WL03','DW03',4,12
declare @s varchar(8000)
set @s='select DWBH'
select @s=@s+',['+WLBH+'_SL]=sum(case WLBH when '''+WLBH+''' then SL end)'
+',['+WLBH+'_JE]=sum(case WLBH when '''+WLBH+''' then JE end)'
from t group by WLBH order by WLBH
set @s=@s+' from t group by DWBH order by DWBH'
exec(@s)
/*
DWBH WL01_SL WL01_JE WL02_SL WL02_JE WL03_SL WL03_JE
---------- ----------- ----------- ----------- ----------- ----------- -----------
DW01 2 10 NULL NULL NULL NULL
DW02 NULL NULL 3 11 NULL NULL
DW03 NULL NULL NULL NULL 4 12
*/
drop table t
insert into t select 'WL01','DW01',2,10
insert into t select 'WL02','DW02',3,11
insert into t select 'WL03','DW03',4,12
declare @s varchar(8000)
set @s='select DWBH'
select @s=@s+',['+WLBH+'_SL]=sum(case WLBH when '''+WLBH+''' then SL end)'
+',['+WLBH+'_JE]=sum(case WLBH when '''+WLBH+''' then JE end)'
from t group by WLBH order by WLBH
set @s=@s+' from t group by DWBH order by DWBH'
exec(@s)
/*
DWBH WL01_SL WL01_JE WL02_SL WL02_JE WL03_SL WL03_JE
---------- ----------- ----------- ----------- ----------- ----------- -----------
DW01 2 10 NULL NULL NULL NULL
DW02 NULL NULL 3 11 NULL NULL
DW03 NULL NULL NULL NULL 4 12
*/
drop table t