declare @s varchar(8000)
set @s=''select @s=@s+',['+rtrim(bxmm)+']=sum(case b.bxmm when '''+rtrim(bxmm)+''' then b.bje else 0 end)' from b group by bxmmset @s='select a.aid,a.axm'+@s+' from a,b where a.aid=b.aid group by a.aid,a.axm'exec(@s)
set @s=''select @s=@s+',['+rtrim(bxmm)+']=sum(case b.bxmm when '''+rtrim(bxmm)+''' then b.bje else 0 end)' from b group by bxmmset @s='select a.aid,a.axm'+@s+' from a,b where a.aid=b.aid group by a.aid,a.axm'exec(@s)
(姓名 varchar(10),数量 int,时间 varchar(10))insert t values ('李四',10,'2005/7')
insert t values ('李四',20,'2005/8')
insert t values ('李四',30,'2005/9')
insert t values ('张三',10,'2005/7')
insert t values ('张三',20,'2005/8')
insert t values ('张三',30,'2005/9')
insert t values ('张三',40,'2005/10')declare @sql varchar(2000)
set @sql='select 姓名'
select @sql=@sql+',['+时间+']=isnull(sum(case when 时间='''+时间+''' then 数量 end),0)'
from t
group by 时间select @sql=@sql+' from t group by 姓名'exec(@sql)
drop table t
姓名 2005/10 2005/7 2005/8 2005/9
---------- ----------- ----------- ----------- -----------
李四 0 10 20 30
张三 40 10 20 30警告: 聚合或其它 SET 操作消除了空值。