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)
解决方案 »
- 求各位兄弟姐妹帮我优化一下查询速度,有酬谢!(表有300百多万条记录)
- 查询结果自动去除了重复行?
- ORA-00942: table or view does not exist
- 关于oracle DBLINK跨三个库的问题,求教!
- 只想查询数据集中满足条件的前10条记录
- 存储过程出错问题
- 请问如何在ORACLE中检索出前两条记录?
- 有没有人知道这个ORACLE安装的问题?
- 启动osm:OracleOraHome81ManagementServer---在线等待
- 救命啊!XP下oracle数据库怎么老是启动不起来啊??
- 菜鸟问题!相当菜了。
- 关于Oracle select...for update of 的问题,请高手帮忙!
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;
实在是不会,楼下的帮忙啊!