Mysql横向数据转换为纵向数据

工作中遇到的一个需求，大概是这样的

mysql查询到的数据如下

现在要把它转换为这种形式

我采用的方法是把每个公司卖的药和所占百分比整理成list，前端再字符串拼接成table

//测试数据,模拟sql返回数据
List<Map> list = new ArrayList<>();
        HashMap map1 = new HashMap();
        map1.put("classId",3);
        map1.put("className","风湿骨痛");
        map1.put("companyId",1);
        map1.put("companyName","大参林");
        map1.put("number",4);
        map1.put("goodsName","云南白药");

        HashMap map2 = new HashMap();
        map2.put("classId",3);
        map2.put("className","风湿骨痛");
        map2.put("companyId",2);
        map2.put("companyName","同仁堂");
        map2.put("number",3);
        map2.put("goodsName","云南白药");

        HashMap map3 = new HashMap();
        map3.put("classId",2);
        map3.put("className","成人用品");
        map3.put("companyId",1);
        map3.put("companyName","大参林");
        map3.put("number",7);
        map3.put("goodsName","排卵试纸");

        HashMap map4 = new HashMap();
        map4.put("classId",1);
        map4.put("className","个人护理");
        map4.put("companyId",3);
        map4.put("companyName","一树");
        map4.put("number",2);
        map4.put("goodsName","妇炎洁");

        list.add(map1);
        list.add(map2);
        list.add(map3);
        list.add(map4);

整理数据 :

        // 获得所有药品的分类
        List<String> cl = new ArrayList<>();
        for (Map m : list) {
            cl.add(m.get("classId").toString());
        }
        //种类去重
        cl = cl.stream().distinct().collect(Collectors.toList());
        // 获取此药品在每个商户的销售数量
        List<Map> result = new ArrayList<>();
        for (String c : cl) {
            List<Map> goodlist = new ArrayList<>();
            for (Map m : list) {
                if (m.get("classId").toString().equals(c)) {
                    goodlist.add(m);
                }
            }
            // 药品总数量
            Integer i = 0;
            for (Map gl : goodlist) {
                i += Integer.valueOf(gl.get("number").toString());
            }
            // 各百分比
            for (Map gl : goodlist) {
                Double sq = Double.valueOf(gl.get("number").toString());
                double ratio = sq / i;
                HashMap<String, Object> mm = new HashMap<>();
                //百分比
                mm.put("ratio", String.format("%.0f", ratio * 100) + "%(" + gl.get("number") + ")");
                mm.put("companyId", gl.get("companyId"));
                mm.put("companyName", gl.get("companyName"));
                mm.put("classId", gl.get("classId"));
                mm.put("className", gl.get("className"));
                result.add(mm);
            }
        }

看一下数据

拼接成Table，这里简单演示

// 获得所有商户
        List<String> merList = new ArrayList<>();
        for (Map m : list) {
            merList.add(m.get("companyName").toString());
        }
        // 去重
        merList = merList.stream().distinct().collect(Collectors.toList());

        // 获得所有药品名
        List<String> cls = new ArrayList<>();
        for (Map m : list) {
            cls.add(m.get("className").toString());
        }
        // 去重
        cls = cls.stream().distinct().collect(Collectors.toList());

        StringBuffer td = new StringBuffer();
        for (int k = 0; k < merList.size(); k++) {
            String  com = merList.get(k);
            td.append("<tr>");
            td.append("<td>").append(com).append("</td>");
            for (int o = 0; o < cls.size(); o++) {
                for (int i = 0; i < result.size(); i++) {
                    if (result.get(i).get("className").equals(cls.get(o)) && result.get(i).get("companyName").equals(com)) {
                        td.append("<td>").append(result.get(i).get("ratio")).append("</td>");
                        break;
                    }
                    if (result.get(i).get("companyName").equals(cls.get(o))) {
                        if (result.get(i).get("companyName") != com) {
                            if (i != result.size() - 1) {
                                continue;
                            } else {
                                td.append("<td>").append("0%(0)").append("</td>");
                                break;
                            }
                        }
                    }
                    if (i == result.size() - 1) {
                        td.append("<td>").append("0%(0)").append("</td>");
                    }
                }
            }
            td.append("</tr>");
        }

结果展示