create table t(编号 varchar(20),店名 varchar(20),机型 varchar(20),[月计划(台)] int)
insert t select 'b01','aa','1100',100
union all select 'b01','aa','2300',80
union all select 'b01','bb','1100',120
union all select 'b01','bb','2300',200
union all select 'b01','cc','1100',15
union all select 'b01','cc','2300',205select * from tdeclare @s varchar(8000)
set @s='select 编号,店名'
select @s=@s+',sum(case 机型 when '''+机型+''' then [月计划(台)] else 0 end)'+'as ['+机型+'(机型)月计划(台)]'
from t group by 机型select @s=@s+' from t group by 编号,店名'
exec (@s)
insert t select 'b01','aa','1100',100
union all select 'b01','aa','2300',80
union all select 'b01','bb','1100',120
union all select 'b01','bb','2300',200
union all select 'b01','cc','1100',15
union all select 'b01','cc','2300',205select * from tdeclare @s varchar(8000)
set @s='select 编号,店名'
select @s=@s+',sum(case 机型 when '''+机型+''' then [月计划(台)] else 0 end)'+'as ['+机型+'(机型)月计划(台)]'
from t group by 机型select @s=@s+' from t group by 编号,店名'
exec (@s)
insert into t select 'b01','aa','1100',100 from dual
union all select 'b01','aa','2300',80 from dual
union all select 'b01','bb','1100',120 from dual
union all select 'b01','bb','2300',200 from dual
union all select 'b01','cc','1100',15 from dual
union all select 'b01','cc','2300',205 from dual
;
commit;select * from tselect 编号,店名,sum(decode(机型,'1100',月计划,0)),sum(decode(机型,'2300',月计划,0)) from t
group by 编号,店名
1 b01 aa 100 80
2 b01 bb 120 200
3 b01 cc 15 205如果机型少的话就写吧!
insert t select 'b01','aa','1100',100
union all select 'b01','aa','2300',80
union all select 'b01','bb','1100',120
union all select 'b01','bb','2300',200
union all select 'b01','cc','1100',15
union all select 'b01','cc','2300',205select * from tdeclare
nvarchar(8000) @s;
set @s:='select 编号,店名';
select @s:=@s+',sum(decode(机型,'1100',月计划,0)),sum(decode(机型,'2300',月计划,0)) ';select @s:=@s+' from t group by 编号,店名';
execute immediate @s;
实在是不会,楼下的帮忙啊!