有如下的表,日期, 项目, 金额
0101 旅游 1000
0101 吃饭 50
0101 吃饭 100
0102 吃饭 120
我想检索出如下结果
旅游 吃饭
0101 1000 150
0102 0 120请问,这能实现吗?
实际数据中,项目列中存得是CODE VALUE,
另有一个表存实际的"旅游""吃饭"等文字信息.我就十分了,请高手指点一下吧.
0101 旅游 1000
0101 吃饭 50
0101 吃饭 100
0102 吃饭 120
我想检索出如下结果
旅游 吃饭
0101 1000 150
0102 0 120请问,这能实现吗?
实际数据中,项目列中存得是CODE VALUE,
另有一个表存实际的"旅游""吃饭"等文字信息.我就十分了,请高手指点一下吧.
MAX(TEMP1.TIME1), TEMP1.WHAT , SUM(TEMP1.MONEY)
FROM TEMP1
GROUP BY TEMP1.WHAT
SELECT
distinct time1
, first_value(a) over (partition by time1 order BY lev DESC) what
, first_value(b) over (partition by time1 order BY lev DESC) money
FROM
(SELECT
time1
, what
, LEVEL lev
, sys_connect_by_path(what,';') a
, sys_connect_by_path(money,';') b
FROM
(SELECT
time1||ROWNUM c
, (time1 ||( ROWNUM - 1)) p
, time1
, what
, money
FROM
temp1)
CONNECT BY
PRIOR c = p)
(riqi integer,
project varchar2(10),
jine integer
)insert into fund
select 0101,'旅游',1000 from dual union all
select 0101,'吃饭',50 from dual union all
select 0101,'吃饭',100 from dual union all
select 0102,'吃饭',120 from dual
select riqi,sum(decode(project,'旅游',jine,0)) 旅游, sum(decode(project,'吃饭',jine,0)) 吃饭 from fund
group by riqi; RIQI 旅游 吃饭
1 0102 0 120
2 0101 1000 150