oracle行转列的施用

oracle行转列的应用
oracle　行转列应用

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等

查看更多 下一篇