数据如下: 编码 时间 数量
1 2009-02 20
1 2009-03 30
1 2009-04 40
2 2009-02 35
2 2009-05 45
变成
编码 2009-02 2009-03 2009-04 2009-05 总计
1 20 30 40 90
2 35 45 80 不知道怎样写?????????????? 急 ORACLE的 谢谢。时间是动态的
1 2009-02 20
1 2009-03 30
1 2009-04 40
2 2009-02 35
2 2009-05 45
变成
编码 2009-02 2009-03 2009-04 2009-05 总计
1 20 30 40 90
2 35 45 80 不知道怎样写?????????????? 急 ORACLE的 谢谢。时间是动态的
语法结构如下: decode (expression, search_1, result_1, search_2, result_2, ., search_n, result_n, default)
decode函数比较表达式和搜索字,如果匹配,返回结果;如果不匹配,返回default值;如果未定义default值,则返回空值。
假设你原来的表名为TABLE1
SQL语句:
SELECT T1.CODE,
SUM(DECODE(T1.TIME, '2009-01', NUM, 0)) 二OO九一月,
SUM(DECODE(T1.TIME, '2009-02', NUM, 0)) 二OO九二月,
SUM(DECODE(T1.TIME, '2009-03', NUM, 0)) 二OO九三月,
SUM(DECODE(T1.TIME, '2009-04', NUM, 0)) 二OO九四月,
SUM(DECODE(T1.TIME, '2009-05', NUM, 0)) 二OO九五月,
SUM(DECODE(T1.TIME, '2009-06', NUM, 0)) 二OO九六月,
SUM(DECODE(T1.TIME, '2009-07', NUM, 0)) 二OO九七月,
SUM(DECODE(T1.TIME, '2009-08', NUM, 0)) 二OO九八月,
SUM(DECODE(T1.TIME, '2009-09', NUM, 0)) 二OO九九月,
SUM(DECODE(T1.TIME, '2009-10', NUM, 0)) 二OO九十月,
SUM(DECODE(T1.TIME, '2009-11', NUM, 0)) 二OO九十一月,
SUM(DECODE(T1.TIME, '2009-12', NUM, 0)) 二OO九十二月
FROM TABLE1 T1
GROUP BY T1.CODE
查询结果:
CODE 二OO九一月 二OO九二月 二OO九三月 二OO九四月 二OO九五月 二OO九六月 二OO九七月 二OO九八月 二OO九九月 二OO九十月 二OO九十一月 二OO九十二月
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ------------
1 0 20 30 40 0 0 0 0 0 0 0 0
2 0 35 0 0 45 0 0 0 0 0 0 注:别名不能是数字开头,如2009-01
select t.costname as costname,
max(substr(to_char(t.djrq,'yyyy-MM-dd'),0,4)) as Year,
sum(decode(substr(to_char(t.djrq,'yyyy-MM-dd'),6,2),'01',t.ybje,null)) as January,
sum(decode(substr(to_char(t.djrq,'yyyy-MM-dd'),6,2),'02',t.ybje,null)) as February,
sum(decode(substr(to_char(t.djrq,'yyyy-MM-dd'),6,2),'03',t.ybje,null)) as March,
sum(decode(substr(to_char(t.djrq,'yyyy-MM-dd'),6,2),'04',t.ybje,null)) as April,
sum(decode(substr(to_char(t.djrq,'yyyy-MM-dd'),6,2),'05',t.ybje,null)) as May,
sum(decode(substr(to_char(t.djrq,'yyyy-MM-dd'),6,2),'06',t.ybje,null)) as June,
sum(decode(substr(to_char(t.djrq,'yyyy-MM-dd'),6,2),'07',t.ybje,null)) as July,
sum(decode(substr(to_char(t.djrq,'yyyy-MM-dd'),6,2),'08',t.ybje,null)) as August,
sum(decode(substr(to_char(t.djrq,'yyyy-MM-dd'),6,2),'09',t.ybje,null)) as September,
sum(decode(substr(to_char(t.djrq,'yyyy-MM-dd'),6,2),'10',t.ybje,null)) as October,
sum(decode(substr(to_char(t.djrq,'yyyy-MM-dd'),6,2),'11',t.ybje,null)) as November,
sum(decode(substr(to_char(t.djrq,'yyyy-MM-dd'),6,2),'12',t.ybje,null)) as December,
sum(t.ybje) as TotalYear,
max(t.id) as id,
max(t.cardcode) cardcode,
max(t.jbr) jbr,
max(t.lrr) lrr,
max(t.shr) shr,
max(t.accounttype) accounttype,
max(t.accountnumber) accountnumber
from account_expense t where t.accounttype is not null and #1 #2
group by t.costname,substr(to_char(t.djrq,'yyyy-MM-dd'),0,4) 提供给你个相似的例子~
select 编码,sum(case when 时间='2009-02' then 数量 else 0 end) as 2009-02,
sum(case when 时间='2009-03' then 数量 else 0 end) as 2009-03,
sum(case when 时间='2009-04' then 数量 else 0 end) as 2009-04,
sum(case when 时间='2009-05' then 数量 else 0 end) as 2009-05,
sum(数量) as 合计
from tab
group by 编码
CREATE OR REPLACE PROCEDURE P_PLAN(P_MAKING_NO VARCHAR2,
P_ITEM_CODE VARCHAR2,
P_TYPE int, --P_TYPE=1 确定的记录 P_TYPE=2 预测的记录
REFCOR OUT SYS_REFCURSOR) IS TYPE datacode_table_type IS TABLE OF mrp_req_plan.req_date%TYPE INDEX BY BINARY_INTEGER;
datacode_table datacode_table_type; V_SQLCMD VARCHAR2(32766) := '';
V_SQLCMD1 VARCHAR2(32766) := '';
P_item_code1 varchar2(200) := '';BEGIN
--确定计划类型
if P_TYPE = 1 then
V_SQLCMD := ' FROM (select a.item_code ,b.item_name ,b.item_desc ,a.standard_genera ,a.key_feature ,a.damageable,a.body ,a.lot,a.mould_status,a.dkl,a.supplier,a.identifier,a.req_date,a.arrival_qty
from mrp_req_plan a inner join edm_master b on a.item_code=b.item_code where a.mrplan_id in (select mrplan_id from mrp_mrpplan_list where making_no=''' ||
P_MAKING_NO || ''')
and a.item_code like ''%' || P_ITEM_CODE ||
'%'' and a.plan_precision=1 order by a.item_code)C group by C.item_code,C.item_name,C.item_desc ,C.standard_genera ,C.key_feature ,C.damageable ,
C.body ,C.lot ,C.mould_status ,C.dkl ,C.supplier ,C.identifier';
P_item_code1 := '%' || P_ITEM_CODE || '%';
--查询符合条件的不同的时间段
select distinct (req_date) BULK COLLECT
into datacode_table
from mrp_req_plan
where mrplan_id in (select mrplan_id
from mrp_mrpplan_list
where making_no = P_MAKING_NO)
and item_code like P_item_code1
and plan_precision = 1
order by req_date;
end if; --预测计划类型
if P_TYPE = 2 then
V_SQLCMD := ' FROM (select a.item_code ,b.item_name ,b.item_desc ,a.standard_genera ,a.key_feature ,a.damageable,a.body ,a.lot,a.mould_status,a.dkl,a.supplier,a.identifier,a.req_date,a.arrival_qty
from mrp_req_plan a inner join edm_master b on a.item_code=b.item_code where a.mrplan_id in (select mrplan_id from mrp_mrpplan_list where making_no=''' ||
P_MAKING_NO || ''')
and a.item_code like ''%' || P_ITEM_CODE ||
'%'' and a.plan_precision=2 order by a.item_code)C group by C.item_code,C.item_name,C.item_desc ,C.standard_genera ,C.key_feature ,C.damageable ,
C.body ,C.lot ,C.mould_status ,C.dkl ,C.supplier ,C.identifier';
P_item_code1 := '%' || P_ITEM_CODE || '%';
--查询符合条件的不同的时间段
select distinct (req_date) BULK COLLECT
into datacode_table
from mrp_req_plan
where mrplan_id in (select mrplan_id
from mrp_mrpplan_list
where making_no = P_MAKING_NO)
and item_code like P_item_code1
and plan_precision = 2
order by req_date;
end if; --通过不同的时间段循环
V_SQLCMD1 := 'select C.item_code 物料编码 ,C.item_name 物料名称,C.item_desc 物料描述,C.standard_genera 标准通用 ,C.key_feature 关键特性,C.damageable 易损件,C.body 外观件,C.lot 批量处理,C.mould_status 模具状态,C.dkl 是否达克罗处理 ,C.supplier 供应商,C.identifier 最新文件标识'; for i in 1 .. datacode_table.COUNT loop
V_SQLCMD1 := V_SQLCMD1 || ', min(decode(C.req_date,''' ||
datacode_table(i) || ''',C.arrival_qty,null)) as" ' ||
datacode_table(i) || '"';
end loop; V_SQLCMD := V_SQLCMD1 || ',sum(C.arrival_qty) 合计' || V_SQLCMD;
Dbms_Output.put_line(V_SQLCMD); OPEN REFCOR FOR V_SQLCMD;END P_PLAN;