declare @sql varchar(8000) set @sql = 'select xh' select @sql = @sql + ' , sum(case kc when ''' + kc + ''' then cj else 0 end) [' + kc + ']' from (select distinct kc from t) as a set @sql = @sql + ' from t group by xh' exec(@sql)
declare @sql varchar(8000) set @sql = 'select xh' select @sql = @sql + ' , sum(case kc when ''' + kc + ''' then cj else 0 end) [' + kc + ']' from (select distinct kc from t) as a set @sql = @sql + ' from t group by xh' print @sql --打印出来SQL语句exec(@sql) exec 是系统提供的可以执行一个串。
先改成 declare @sql varchar(8000) set @sql = 'select xh' select @sql = @sql + ' , sum(case kc when ''' + kc + ''' then cj else 0 end) [' + kc + ']' from (select distinct kc from t) as a set @sql = @sql + ' from t group by xh' print @sql执行, 再改成 declare @sql varchar(8000) set @sql = 'select xh' select @sql = @sql + ' , sum(case kc when ''' + kc + ''' then cj else 0 end) [' + kc + ']' from (select distinct kc from t) as a print @sql 执行 我想你就基本明白了
这语句如下写也许号理解些declare @sql varchar(8000) set @sql = 'select xh' select @sql = @sql + ' , sum(case kc when ''' + kc + ''' then cj else 0 end) [' + kc + ']' from t group by kcset @sql = @sql + ' from t group by xh' exec(@sql)
sum(case kc when ''' + kc + ''' then cj else 0 end) [' + kc + ']' 这个是关键
sum(case kc when ''' + kc + ''' then cj else 0 end) --是指对下面查出的 KC列的值是 'kc'时对 cj列 求和,不是为0所以不被求和 from (select distinct kc from t) as a (a 是指括号里查询出来的数据集的别名)
[' + kc + ']'是给前面的和一个别名kc
exec(@sql) --改为print(@sql) 执行以下语句create table t(kc varchar(10)) insert tt select 'cc' union all select 'cc' union all select 'bb' union all select 'bb' 再执行改成print的那段得到 select xh , sum(case kc when 'bb' then cj else 0 end) [bb] , sum(case kc when 'cc' then cj else 0 end) [cc] from t group by xh
set @sql = 'select xh'
select @sql = @sql + ' , sum(case kc when ''' + kc + ''' then cj else 0 end) [' + kc + ']'
from (select distinct kc from t) as a
set @sql = @sql + ' from t group by xh'
print @sql --打印出来SQL语句exec(@sql)
exec 是系统提供的可以执行一个串。
declare @sql varchar(8000)
set @sql = 'select xh'
select @sql = @sql + ' , sum(case kc when ''' + kc + ''' then cj else 0 end) [' + kc + ']'
from (select distinct kc from t) as a
set @sql = @sql + ' from t group by xh'
print @sql执行,
再改成
declare @sql varchar(8000)
set @sql = 'select xh'
select @sql = @sql + ' , sum(case kc when ''' + kc + ''' then cj else 0 end) [' + kc + ']'
from (select distinct kc from t) as a
print @sql
执行
我想你就基本明白了
set @sql = 'select xh'
select @sql = @sql + ' , sum(case kc when ''' + kc + ''' then cj else 0 end) [' + kc + ']'
from t
group by kcset @sql = @sql + ' from t group by xh'
exec(@sql)
'kc'时对 cj列 求和,不是为0所以不被求和
from (select distinct kc from t) as a (a 是指括号里查询出来的数据集的别名)
insert tt select 'cc'
union all select 'cc'
union all select 'bb'
union all select 'bb'
再执行改成print的那段得到
select xh , sum(case kc when 'bb' then cj else 0 end) [bb] ,
sum(case kc when 'cc' then cj else 0 end) [cc] from t group by xh