oracle 行转列应用
表结构如下:
aid wellitem_id well_id material_code material_reserves daily_date
1 01 001 1001 25.03 2012-12-12
2 01 001 1002 43.22 2012-12-12
3 02 001 1001 55.03 2012-12-12
4 02 001 1002 44.22 2012-12-12
查询结果如下:
wellitem_id WELLITEM_NAME well_id WELL_NAME rh kc
01 a 001 aa 25.03 43.22
02 b 001 bb 55.03 44.22
关键点是,要转换的字段要用聚合函处理,如max,min,sum等
select t.WELLITEM_ID,
co.ORG_ABBREVIATION as WELLITEM_NAME,
t.WELL_ID,
cb.WELL_NAME
max(decode(MATERIAL_CODE, '1001', MATERIAL_RESERVES,null)) as rh, -- 日耗
max(decode(MATERIAL_CODE, '1002', MATERIAL_RESERVES,null)) as kc -- 库存
from MATERIAL t
LEFT JOIN COMM_ORG_INFORMATION co ON(
t.WELLITEM_ID = co.ORG_ID
)
LEFT JOIN COMM_BASE_WELL_INFO cb ON(
t.WELL_ID = cb.WELL_ID
)
where TO_CHAR(t.daily_date,'yyyy-mm-dd')='2012-12-12'
group by t.WELLITEM_ID,t.well_id,co.ORG_ABBREVIATION,cb.WELL_NAME
表结构如下:
aid wellitem_id well_id material_code material_reserves daily_date
1 01 001 1001 25.03 2012-12-12
2 01 001 1002 43.22 2012-12-12
3 02 001 1001 55.03 2012-12-12
4 02 001 1002 44.22 2012-12-12
查询结果如下:
wellitem_id WELLITEM_NAME well_id WELL_NAME rh kc
01 a 001 aa 25.03 43.22
02 b 001 bb 55.03 44.22
关键点是,要转换的字段要用聚合函处理,如max,min,sum等