declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',['+TYPE+TYPE2+']=sum(case TYPE+TYPE2 when '''+TYPE+TYPE2+''' then 數量 else 0 end)'+',['+TYPE+TYPE2+'c]=sum(case TYPE+TYPE2 when '''+TYPE+TYPE2+''' then 1 else 0 end)'from tb group by ID,TYPE,TYPE2
exec('select 名稱'+@sql+' from tb group by 名稱')
set @sql=''
select @sql=@sql+',['+TYPE+TYPE2+']=sum(case TYPE+TYPE2 when '''+TYPE+TYPE2+''' then 數量 else 0 end)'+',['+TYPE+TYPE2+'c]=sum(case TYPE+TYPE2 when '''+TYPE+TYPE2+''' then 1 else 0 end)'from tb group by ID,TYPE,TYPE2
exec('select 名稱'+@sql+' from tb group by 名稱')
set @sql=''
select @sql=@sql+',['+TYPE+TYPE2+']=sum(case TYPE+TYPE2 when '''+TYPE+TYPE2+''' then 數量 else 0 end)'+',['+TYPE+TYPE2+'c]=sum(case TYPE+TYPE2 when '''+TYPE+TYPE2+''' then 1 else 0 end)'from tb group by ID,TYPE,TYPE2
exec('select ID'+@sql+' from tb group by ID,TYPE,TYPE2 ')
因为
TYPE2是INT
要改成
['+TYPE+CONVERT(char(5),TYPE2)+']
但是还是不行,,
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',['+TYPE+CONVERT(char(5),TYPE2)++']=sum(case TYPE+TYPE2 when '''+TYPE+CONVERT(char(5),TYPE2)++''' then 數量 else 0 end)'+',['+TYPE+CONVERT(char(5),TYPE2)++'c]=sum(case TYPE+TYPE2 when '''+TYPE+CONVERT(char(5),TYPE2)++''' then 1 else 0 end) from tb group by ID,TYPE,TYPE2 '
exec('select ID'+@sql+' from tb group by ID,TYPE,TYPE2 ')
还是不行,,提示TYPE和TYPE2无效。
不知这两个在程序中定义过没有,如果没有就要在存储过程中定义!
如果有,在执行这个存储过程的时候加参数!
A1=case when (type=a and type2=1) then sum(mo),
a1c=(select count(*) from 表一 where (type=a and type2=1)),
A2=case when (type=a and type2=2) then sum(mo),
a2c=(select count(*) from 表一 where (type=a and type2=2))
from 表一
create table 表一(ID int,TYPE varchar(10),MO int,TYPE2 int)
insert 表一 select 290,'A',100,1
union all select 290,'B',100,1
union all select 291,'A',100,1
union all select 291,'A',100,2
union all select 291,'A',100,2
union all select 293,'A',100,2
go--查询处理
declare @s varchar(8000)
set @s=''
select @s=@s+',['+rtrim(TYPE)+cast(TYPE2 as varchar)
+']=sum(case when TYPE='''+rtrim(TYPE)
+''' and TYPE2='+cast(TYPE2 as varchar)
+' then MO else 0 end),['+rtrim(TYPE)+cast(TYPE2 as varchar)
+'C]=sum(case when TYPE='''+rtrim(TYPE)
+''' and TYPE2='+cast(TYPE2 as varchar)
+' then 1 else 0 end)'
from 表一
group by TYPE,TYPE2
exec('select ID'+@s+' from 表一 group by id')
go--删除测试
drop table 表一/*--测试结果ID A1 A1C A2 A2C B1 B1C
----- ----- ----- ----- ----- ----- ------
290 100 1 0 0 100 1
291 100 1 200 2 0 0
293 0 0 100 1 0 0
--*/