# 请大家告诉我是否真的无解

yjcxg 2004-05-06 10:13:24

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

...全文
71 19 点赞 打赏 收藏 举报

19 条回复

8992026 2004-05-06

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
• 打赏
• 举报

zjcxc 2004-05-06
--关于原理,以我上面的测试数据为例,楼主看看下面两个语句执行的结果,应该可以交叉表的处理原理

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

/*--测试结果

F_NM1 F_101 F_102 F_103
----------- ----------- ----------- -----------
1 1000 0 0
1 0 2000 0
1 0 0 99
2 3000 0 0
2 0 4000 0
2 0 0 599

（所影响的行数为 6 行）

F_NM1 F_101 F_102 F_103
----------- ----------- ----------- -----------
1 1000 2000 99
2 3000 4000 599

（所影响的行数为 2 行）

--*/
• 打赏
• 举报

8992026 2004-05-06

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)

f_nm1 f_nm2_101 f_nm2_102 f_nm2_103
----------- ----------- ----------- -----------
1 1000 2000 99
2 3000 4000 599
• 打赏
• 举报

zjcxc 2004-05-06
--这是典型的交叉表

--测试

--测试数据

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

--删除测试
drop table 表

/*--测试结果
F_NM1 F_101 F_102 F_103
----------- ----------- ----------- -----------
1 1000 2000 99
2 3000 4000 599

--*/
• 打赏
• 举报

8992026 2004-05-06
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)
• 打赏
• 举报

yjcxg 2004-05-06

F_NM1(int) F_NM2(int) F_QTY(int)
1 101 1000
1 102 2000
1 103 99
2 101 3000
2 102 4000
2 103 599

F_NM1 F_101 F_102 F_103
1 1000 2000 99
2 3000 4000 599
3

• 打赏
• 举报

yjcxg 2004-05-06
UP,请原谅我的无知,请能否再解释一下
• 打赏
• 举报

zjcxc 2004-05-06

• 打赏
• 举报

yjcxg 2004-05-06

F_NM1 是INT型,F_NM2也是INT型,只是这两个列的数据都是未知的,

F_QTY当然也INT型的

• 打赏
• 举报

20分提如此难度的问题，也太低了吧。
• 打赏
• 举报

txlicenhe 2004-05-06

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)
• 打赏
• 举报

yjcxg 2004-05-06

• 打赏
• 举报

txlicenhe 2004-05-06

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
• 打赏
• 举报

yjcxg 2004-05-06

• 打赏
• 举报

zjcxc 2004-05-06

• 打赏
• 举报

yjcxg 2004-05-06

• 打赏
• 举报

zjcxc 2004-05-06
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')

--显示结果
select * from ##t

--删除临时表
drop table ##t
• 打赏
• 举报

yjcxg 2004-05-06

• 打赏
• 举报

yjcxg 2004-05-06

• 打赏
• 举报

2.1w+

MS-SQL Server 疑难问题

2004-05-06 10:13