declare @sql varchar(4000)set @sql=''
select @sql=@sql + ',case when 商品大类='''+商品大类+''' then 数量 else 0 end as '
+ 商品大类+'数量','case when 商品大类='''+商品大类+''' then 金额 else 0 end as '
+ 商品大类+'金额'
from (select distinct 商品大类 from XSBB) aa
exec ('select 客户名称 '+ @sql + ' from xsbb group by 客户名称')
select @sql=@sql + ',case when 商品大类='''+商品大类+''' then 数量 else 0 end as '
+ 商品大类+'数量','case when 商品大类='''+商品大类+''' then 金额 else 0 end as '
+ 商品大类+'金额'
from (select distinct 商品大类 from XSBB) aa
exec ('select 客户名称 '+ @sql + ' from xsbb group by 客户名称')
create table #(a varchar(100),b int)
insert # values('aa',11)
insert # values('bb',1)
insert # values('aa',45)
insert # values('cc',81)
insert # values('a',11)
insert # values('aay',561)
insert # values('a',14)declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as aselect @sql = left(@sql,len(@sql)-1) + ' from #'exec(@sql)drop table #
create table xsbb(客户名称 varchar(10),商品大类 varchar(10),数量 money,金额 money)
insert into xsbb
select 'DELL','OSC',1200,2400
union select 'DELL','CRYSTALL',36.5,110.5
union select 'DELL','ZT',250,1000
union select 'FOX','OSC',1280,2560
union select 'FOX','ZT',75.68,302.6
union select 'HP','RN',2000,10000
union select 'HP','OSC',658.5,1317
union select 'HP','ZT',1250,5000
union select 'SAMSUNG','OSC',25000,50000
union select 'SAMSUNG','RN',1200,6000
union select 'DELL','CRYSTALL',136.5,410.5
union select 'DELL','ZT',20,100
union select 'FOX','OSC',128,260
union select 'FOX','ZT',5.68,30.6
union select 'HP','RN',20,100
union select 'HP','OSC',68.5,117
union select 'HP','ZT',10,50
union select 'SAMSUNG','OSC',200,500
union select 'SAMSUNG','RN',10,60
go--用存储过程来实现
create procedure pbuild
as
declare @sql varchar(1000),@fd varchar(100)
declare #aa cursor for select distinct 商品大类 from xsbb order by 商品大类select @sql='select 客户名称 '
open #aa
fetch next from #aa into @fd
while @@fetch_status=0
begin
set @sql=@sql
+',sum(isnull(case 商品大类 when '''+@fd
+''' then 数量 end,0)) as ['+@fd+'数量]'
+',sum(isnull(case 商品大类 when '''+@fd
+''' then 金额 end,0)) as ['+@fd+'金额]'
fetch next from #aa into @fd
end
close #aa
deallocate #aa
set @sql=@sql+' from xsbb group by 客户名称'
print @sql
exec(@sql)go--测试
exec pbuild --删除数据测试环境
drop table xsbb
drop procedure pbuild
1,用ado
SELECT 客户名称,商品大类,SUM(数量)AS 数量,SUM(金额) AS 金额 FROM XSBB GROUP BY 客户名称,商品大类 ORDER BY 客户名称,商品大类
返回这个最少记录到客户端:设为rst1
作rst2静态记录集:从商品大类中动态生成:OSC数量 OSC金额 CRYSTALL数量 CRYSTALL金额 ZT数量 ZT金额 RN数量
从rst1读到rst2
2,后台游标(性能不好,数据库是针对关系而不是循环的)
create table jiaocha_611(name varchar(5),subject nchar(4),score numeric(3,0)) go
insert into jiaocha_611
values('kf','语文','60')
go不确定列的交叉表:
declare @sql varchar(8000)
set @sql = 'select name,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then score else 0 end) as '''+subject+''',' from (select distinct subject from administrator.jiaocha_611) as a
select @sql = left(@sql,len(@sql)-1) + ' from administrator.jiaocha_611 group by name'
exec(@sql)
go
的存储过程写了和没写一样,整一个就是SQL语句..应该写一个任意表的转换过程
表示非常感谢。