oracle decode行列转换

使用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转换成的数据