求一个行列转换的写法（ORACLE）
create table t_testHL
(
number varchar2(20),
node varchar2(20),
qty number(6)
bizDate Date
);
有以下数据
bizDate number node qty
2009-12-1 A 工序A 30
2009-12-1 B 工序C 40
2009-12-2 A 工序B 30
2009-12-3 A 工序C 40
2009-12-4 B 工序D 30
2009-12-5 A 工序A 10
bizDate从2009-12-1到2009-12-4结果为
number 工序A 工序B 工序C 工序D
A 30 30 40
B 40 30
合计 30 30 80 30
bizDate从2009-12-1到2009-12-5结果为
number 工序A 工序B 工序C 工序D
A 40 30 40
B 40 30
合计 40 30 80 30
bizDate从2009-12-1到2009-12-3结果为
number 工序A 工序B 工序C
A 30 30 40
B 40
合计 30 30 80
如果横向合计也能出来的话 那更好，多谢了！！
------解决方案--------------------
- SQL code
with t_dec as(select to_date('2009-12-1','yyyy-mm-dd') bizDate, 'A' number_, '工序A' node, 30 qty from dual union allselect to_date('2009-12-1','yyyy-mm-dd') bizDate, 'B' number_, '工序C' node, 40 qty from dual union allselect to_date('2009-12-2','yyyy-mm-dd') bizDate, 'A' number_, '工序B' node, 30 qty from dual union allselect to_date('2009-12-3','yyyy-mm-dd') bizDate, 'A' number_, '工序C' node, 40 qty from dual union allselect to_date('2009-12-4','yyyy-mm-dd') bizDate, 'B' number_, '工序D' node, 30 qty from dual union allselect to_date('2009-12-5','yyyy-mm-dd') bizDate, 'A' number_, '工序A' node, 10 qty from dual)select number_,sum(decode(node,'工序A',qty)) 工序A,sum(decode(node,'工序B',qty)) 工序B,sum(decode(node,'工序C',qty)) 工序C,sum(decode(node,'工序D',qty)) 工序Dfrom t_decwhere bizDate between to_date('2009-12-1','yyyy-mm-dd') and to_date('2009-12-5','yyyy-mm-dd')group by number_union allselect '合计' bizDate, sum(工序A), sum(工序B), sum(工序C), sum(工序D)from(select number_,sum(decode(node,'工序A',qty)) 工序A,sum(decode(node,'工序B',qty)) 工序B,sum(decode(node,'工序C',qty)) 工序C,sum(decode(node,'工序D',qty)) 工序Dfrom t_decwhere bizDate between to_date('2009-12-1','yyyy-mm-dd') and to_date('2009-12-5','yyyy-mm-dd')group by number_);