590
社区成员
发帖
与我相关
我的任务
分享
create table test(type varchar(10), n int, m int)
go
insert into test values('A1', 1, 10),('A2', 2, 12),('A3', 3, 13)
go
declare @sql varchar(2000)
set @sql = 'select '
select @sql = @sql +
+'sum(case when type = ''' + type + ''' then n else 0 end) As ' + type + '_n,'
+'sum(case when type = ''' + type + ''' then m else 0 end) As ' + type + '_m,'
from test ;
set @sql = substring(@sql,1,len(@sql)-1) + ' from test'
exec (@sql)
go
drop table test
go
(3 行受影响)
A1_n A1_m A2_n A2_m A3_n A3_m
----------- ----------- ----------- ----------- ----------- -----------
1 10 2 12 3 13
(1 行受影响)
select @sql = @sql +
+'sum(case when type = ''' + type + ''' then n else 0 end) As ' + type + '_n,'
+'sum(case when type = ''' + type + ''' then m else 0 end) As ' + type + '_m,'
from (
select distinct type test ) x ;
create table test(type varchar(10), n int, m int)
go
insert into test values('A1', 1, 10),('A2', 2, 12),('A3', 3, 13)
go
-- 创建存储过程
create proc sp_data
as
declare @sql varchar(2000)
set @sql = 'select '
select @sql = @sql +
+'sum(case when type = ''' + type + ''' then n else 0 end) As ' + type + '_n,'
+'sum(case when type = ''' + type + ''' then m else 0 end) As ' + type + '_m,'
from test ;
set @sql = substring(@sql,1,len(@sql)-1) + ' from test'
exec (@sql)
go
-- 执行他
exec sp_data
go
drop proc sp_data
go
drop table test
go