oracle decode行列转换

来源：互联网发布：北京交大网络教育学院编辑：程序博客网时间：2024/06/01 14:46

使用decode可以把查到的数据进行行列转换不需要再去java里处理使很多行数据整合为一条

StringBuffer sql = new StringBuffer(1024);        String decodeSql = new String();        List<Map<String,Object>> list = aRDao.getTableHead(liqGapId);        list.remove(0);        sql.append(" select t.LIQNAME GAP_NAME, ");        for(int i=0;i<list.size();i++) {            decodeSql += " sum(decode(t.XLIQGAPID, ";             decodeSql += list.get(i).get("GAP_ID");            decodeSql += ", t.value, null)) as value";            decodeSql += i;            decodeSql += ",";        }        sql.append(decodeSql.substring(0, decodeSql.length()-1));        sql.append(" from (select mt.Y_LIQ_GAP_ID YLIQGAPID, ");        sql.append(" bg.name         LIQNAME, ");        sql.append(" mt.val          VALUE, ");        sql.append(" mt.X_LIQ_GAP_ID XLIQGAPID ");        sql.append(" from MOD_REAL_GAP_MATRIX_TB mt ");        sql.append(" left join BAS_GAP_TB bg ");        sql.append(" on mt.Y_LIQ_GAP_ID = bg.id ");        sql.append(" where mt.CARD_REAL_ID =");        sql.append(id);        sql.append(" order by mt.Y_LIQ_GAP_ID desc, mt.X_LIQ_GAP_ID) t ");        sql.append(" group by t.LIQNAME, t.yliqgapid ");        sql.append(" order by t.yliqgapid ");

list为你动态获取的表头就是列

select t.LIQNAME,       sum(decode(t.XLIQGAPID, 1704, t.value, null)) as value0,       sum(decode(t.XLIQGAPID, 1705, t.value, null)) as value1  from (select mt.Y_LIQ_GAP_ID YLIQGAPID,               bg.name         LIQNAME,               mt.val          VALUE,               mt.X_LIQ_GAP_ID XLIQGAPID          from MOD_REAL_GAP_MATRIX_TB mt          left join BAS_GAP_TB bg            on mt.Y_LIQ_GAP_ID = bg.id         where mt.CARD_REAL_ID = 62         order by mt.Y_LIQ_GAP_ID desc, mt.X_LIQ_GAP_ID) t group by t.LIQNAME, t.yliqgapid order by t.yliqgapid

这是子查询的数据，也就是原来要传到后台进行处理整合的数据

这是通过decode转换成的数据

0 0