请大家注意只能用SQLSERVER存储过程,不能用任何语言
现有表
F_NM1(varchar) F_NM2(varchar) F_QTY(int)
A 01 900
A 02 1000
B 01 2000
B 02 3000
如何改写成
F_NM1 F_NM2_01 F_NM2_02
A 900 1000
B 2000 3000
...全文
10319打赏收藏
请大家告诉我是否真的无解
请大家注意只能用SQLSERVER存储过程,不能用任何语言 现有表 F_NM1(varchar) F_NM2(varchar) F_QTY(int) A 01 900 A 02 1000 B 01 2000 B 02 3000 如何改写成 F_NM1 F_NM2_01 F_NM2_02 A 900 1000 B 2000 3000
就是要凑出一下语句来执行:
select f_nm1,sum(case f_nm2 when 101 then f_qty end) [ f_nm2_101],sum(case f_nm2 when 102 then f_qty end) [ f_nm2_102],sum(case f_nm2 when 103 then f_qty end) [ f_nm2_103] from testcc group by f_nm1
有个警告,改一下就没有了
declare @sql varchar(8000)
set @sql = 'select f_nm1'
select @sql = @sql + ',sum(case f_nm2 when '+cast(f_nm2 as varchar(10))+' then f_qty else 0 end) [ f_nm2_'+cast(f_nm2 as varchar(10))+']'
from (select distinct f_nm2 from testcc) as a
select @sql = @sql+' from testcc group by f_nm1'
exec(@sql)
凑出的语句是:
select f_nm1,sum(case f_nm2 when 101 then f_qty else 0 end) [ f_nm2_101],sum(case f_nm2 when 102 then f_qty else 0 end) [ f_nm2_102],sum(case f_nm2 when 103 then f_qty else 0 end) [ f_nm2_103] from testcc group by f_nm1
select F_NM1
,[F_101]=case F_NM2 when 101 then F_QTY else 0 end
,[F_102]=case F_NM2 when 102 then F_QTY else 0 end
,[F_103]=case F_NM2 when 103 then F_QTY else 0 end
from 表
select F_NM1
,[F_101]=sum(case F_NM2 when 101 then F_QTY else 0 end)
,[F_102]=sum(case F_NM2 when 102 then F_QTY else 0 end)
,[F_103]=sum(case F_NM2 when 103 then F_QTY else 0 end)
from 表
group by F_NM1
测试:
create table testcc (F_NM1 int,F_NM2 int, F_QTY int)
go
insert testcc
select
1, 101, 1000
union all
select
1, 102, 2000
union all
select
1, 103, 99
union all
select
2, 101, 3000
union all
select
2, 102, 4000
union all
select
2, 103, 599
go
declare @sql varchar(8000)
set @sql = 'select f_nm1'
select @sql = @sql + ',sum(case f_nm2 when '+cast(f_nm2 as varchar(10))+' then f_qty end) [ f_nm2_'+cast(f_nm2 as varchar(10))+']'
from (select distinct f_nm2 from testcc) as a
select @sql = @sql+' from testcc group by f_nm1'
exec(@sql)
create table 表(F_NM1 int,F_NM2 int,F_QTY int)
insert 表 select 1,101,1000
union all select 1,102,2000
union all select 1,103,99
union all select 2,101,3000
union all select 2,102,4000
union all select 2,103,599
go
--因为 F_NM2 的值未知,所以得用动态的SQL语句
declare @s varchar(8000)
set @s=''
select @s=@s+',[F_'+cast(F_NM2 as varchar)
+']=sum(case F_NM2 when '+cast(F_NM2 as varchar)
+' then F_QTY else 0 end)'
from 表
group by F_NM2
exec('select F_NM1'+@s+' from 表 group by F_NM1')
go
declare @sql varchar(8000)
set @sql = 'select f_nm1'
select @sql = @sql + ',sum(case f_nm2 when '+cast(f_nm2 as varchar(10))+' then f_qty end) [ f_nm2_'+cast(f_nm2 as varchar(10))+']'
from (select distinct f_nm2 from 你的表) as a
select @sql = @sql+' from 你的表 group by f_nm1'
exec(@sql)
declare @sql varchar(8000)
set @sql = 'select f_nm1'
select @sql = @sql + ',sum(case f_nm2 when '''+f_nm2+''' then f_qty end) [ f_nm2_'+f_nm2+']'
from (select distinct f_nm2 from 你的表) as a
select @sql = @sql+' from 你的表 group by f_nm1'
exec(@sql)
select f_nm1,
sum(case when f_nm2='01' then f_qty else 0 end) as f_nm2_01,
sum(case when f_nm2='02' then f_qty else 0 end) as f_nm2_02
from 你的表
group by f_nm1
declare @s varchar(8000)
set @s=''
select @s=@s+',[F_'+cast(F_NM2 as varchar)
+']=sum(case F_NM2 when '+cast(F_NM2 as varchar)
+' then F_QTY else 0 end)'
from 表
group by F_NM2
exec('select F_NM1'+@s+'
into ##t --用全局的临时表才行
from 表 group by F_NM1')