---------下面的例子請參考---------------- Create Table A(id int,book varchar(20)) insert into A select 1,'書' union select 2,'筆' Create Table B(id int, aid int,qi int,pro varchar(20),sh varchar(20)) insert into B select 1,2,10,'廣西','南寧' union select 2,2,10,'廣東','深圳' union select 2,1,9,'四川','成都' ---------------實現語句-------------------------- declare @s varchar(4000) set @s='' select @s=@s+pro+sh+'=isnull((select sum(qi) from b where aid=c.aid and pro+sh='+(case when pro='廣東' or pro='廣西' then ''''+pro+'''' else ''''+ pro+sh +'''' end)+'),0),' from b group by pro,sh set @s=left(@s,len(@s)-1) set @s='select aid,name=(select book from A where id=c.aid),'+@s+' from B c group by aid order by aid ' exec( @s)
------修正後的----------下面是樓主要的東東---------------- Create Table A(id int,book varchar(20)) insert into A select 1,'書' union select 2,'筆' Create Table B(id int, aid int,qi int,pro varchar(20),sh varchar(20)) insert into B select 1,2,10,'廣西','南寧' union select 2,2,10,'廣東','深圳' union select 2,1,9,'四川','成都' ---------------實現語句-------------------------- declare @s varchar(4000) set @s='' select @s=@s+pro+sh+'=isnull((select sum(qi) from b where aid=c.aid and '+(case when pro='廣東' or pro='廣西' then 'pro' else 'pro+sh' end)+'='+ (case when pro='廣東' or pro='廣西' then ''''+pro+'''' else ''''+ pro+sh +'''' end)+'),0),' from b group by pro,sh set @s=left(@s,len(@s)-1) set @s='select aid,name=(select book from A where id=c.aid),'+@s+' from B c group by aid order by aid ' exec(@s) ---------------------------結果--------------------------------------------------- ID 产品名字 广西南宁 广东深圳 四川成都 ------------------------------------------- 1 书 0 0 10 2 笔 10 10 0
declare @s nvarchar(4000) set @s=N'' select @s=@s+N','+a+N'=sum(case when 销售省='+quotename(b,N'''') +case when c=N'' then N'' else N' and 销售市='+quotename(c,N'''') end +' then 销售金额 else 0 end)' from( select distinct a=quotename(case when 销售省 in(N'广东',N'广西') then rtrim(销售省) else rtrim(销售省)+rtrim(销售市) end), b=rtrim(销售省), c=case when 销售省 in(N'广东',N'广西') then N'' else rtrim(销售市) end from B表)a exec(N'select a.ID,a.产品名字'+@s+N' from A表 a,B表 b where a.ID=b.A表ID group by a.ID,a.产品名字')
[''+rtrim(A.省)+''_S]=
要这个.........列名...
记录着产品
ID 产品名字
------------------------------
1 书
2 笔
B表
记录产品销售信息..
ID A表ID 销售金额 销售省 销售市
--------------------------------------
1 2 10 广西 南宁
2 2 10 广东 深圳
2 1 9 四川 成都现在上面的存储过程的
是那样的,统计出来..
ID 产品名字 广西南宁 广东深圳 四川成都
-------------------------------------------
1 书 0 0 10
2 笔 10 10 0
以此类推...现在我想做成,,,,
如果遇见广西和广东的..整个省份都计算,,不用计算城市..其他的还是计算城市...
Create Table A(id int,book varchar(20))
insert into A
select 1,'書' union select 2,'筆'
Create Table B(id int, aid int,qi int,pro varchar(20),sh varchar(20))
insert into B
select 1,2,10,'廣西','南寧' union
select 2,2,10,'廣東','深圳' union
select 2,1,9,'四川','成都'
---------------實現語句--------------------------
declare @s varchar(4000)
set @s=''
select @s=@s+pro+sh+'=isnull((select sum(qi) from b where aid=c.aid and pro+sh='+(case when pro='廣東' or pro='廣西' then ''''+pro+'''' else ''''+ pro+sh +'''' end)+'),0),'
from b group by pro,sh
set @s=left(@s,len(@s)-1)
set @s='select aid,name=(select book from A where id=c.aid),'+@s+' from B c group by aid order by aid '
exec( @s)
Create Table A(id int,book varchar(20))
insert into A
select 1,'書' union select 2,'筆'
Create Table B(id int, aid int,qi int,pro varchar(20),sh varchar(20))
insert into B
select 1,2,10,'廣西','南寧' union
select 2,2,10,'廣東','深圳' union
select 2,1,9,'四川','成都'
---------------實現語句--------------------------
declare @s varchar(4000)
set @s=''
select @s=@s+pro+sh+'=isnull((select sum(qi) from b where aid=c.aid and '+(case when pro='廣東' or pro='廣西' then 'pro' else 'pro+sh' end)+'='+
(case when pro='廣東' or pro='廣西' then ''''+pro+'''' else ''''+ pro+sh +'''' end)+'),0),'
from b group by pro,sh
set @s=left(@s,len(@s)-1)
set @s='select aid,name=(select book from A where id=c.aid),'+@s+' from B c group by aid order by aid '
exec(@s)
---------------------------結果---------------------------------------------------
ID 产品名字 广西南宁 广东深圳 四川成都
-------------------------------------------
1 书 0 0 10
2 笔 10 10 0
declare @s nvarchar(4000)
set @s=N''
select @s=@s+N','+a+N'=sum(case when 销售省='+quotename(b,N'''')
+case when c=N'' then N'' else N' and 销售市='+quotename(c,N'''') end
+' then 销售金额 else 0 end)'
from(
select distinct
a=quotename(case when 销售省 in(N'广东',N'广西') then rtrim(销售省) else rtrim(销售省)+rtrim(销售市) end),
b=rtrim(销售省),
c=case when 销售省 in(N'广东',N'广西') then N'' else rtrim(销售市) end
from B表)a
exec(N'select a.ID,a.产品名字'+@s+N'
from A表 a,B表 b
where a.ID=b.A表ID
group by a.ID,a.产品名字')