select 中間的表達式
困己久， 如下表：
NO qty type
su 21 a
su 34 c
su 45 b
跟據字段值生成新字段:
NO total a c b
su 100 21 34 45
我想在select 中間加表判斷達式應該能實現，可我不會寫望高手指點
问题点数：20、回复次数：3Top
1 楼tj_dns（愉快的登山者）回复于 2003-11-04 12:05:14 得分 10
declare @s varchar(8000)
set @s = 'select NO, sum(qty) [total], '
select @s = @s + ',sum(case when type ='''+ type + ''' then qty else 0 end) as [' +type+']'
from (select distinct type from table1) A
set @s = @s + ' from table1 group by NO'
exec (@s)Top
2 楼pengdali（）回复于 2003-11-04 12:08:06 得分 10
declare @sql varchar(8000)
set @sql = 'select No'
select @sql = @sql + ',max(case type when '''+type+''' then qty end) ['+type+']'
from (select distinct type from 有一表) as a
select @sql = @sql+' from 有一表 group by No'
exec(@sql)
goTop
3 楼pengdali（）回复于 2003-11-04 12:09:20 得分 0
declare @sql varchar(8000)
set @sql = 'select No,sum(qty) total'
select @sql = @sql + ',sum(case type when '''+type+''' then qty else 0 end) ['+type+']'
from (select distinct type from 有一表) as a
select @sql = @sql+' from 有一表 group by No'
exec(@sql)
go
Top
