Oracle行转列SQL后台代码

String mapSql = "";

  for(String key : map.keySet()){

   Map<String, String> tempMap = map.get(key);

   for(String tempKey : tempMap.keySet()){

    colName = tempMap.get(tempKey);

    colCode = tempKey;

    if(!"".equals(mapSql)){

     mapSql += " UNION ALL ";

    }

    StringBuffer sqs = new StringBuffer();

    sqs.append("SELECT '"+colCode+"', T.* FROM (WITH pivot_data AS (SELECT '"+ colName +"',"+ colCode +", TAB_YEAR ");

    sqs.append("FROM US_APP."+key+")  SELECT *  FROM   pivot_data  PIVOT (SUM("+ colCode +")");

    sqs.append("FOR TAB_YEAR IN  ("+ years +" ) )) T ");

    mapSql += sqs.toString();

   }

  }

  List<Object[]> _querylist = genericDao.getDataWithNativeSql(mapSql);

 

  StringBuffer bf = new StringBuffer();

  if(_querylist.size() > 0){

   for(int i = 0; i < _querylist.size(); i++){

    Object[] obj = (Object[]) _querylist.get(i);

    int size = _listTabYear.size();

    bf.append("{codeName:'"+obj[0]+"',");

    bf.append("gdpName:'"+obj[1]+"',");

    for(int t = 0; t < size; t++){

     Object[]  tempObj = _listTabYear.get(t);

     bf.append(tempObj[0] + ":'"+obj[t+2]+"'");

     if(t == size - 1){

      bf.append("}");

     }

     bf.append(",");

    }

   }

   return "{success : true, gridData : [" + bf.substring(0, bf.length() -1) + "], fieldsCode : ["+years+"]}";

  }