行转列实现方式总结

前言

在日常开发中遇到了，需要对表中数据某个字段行数据转成列，个人觉得这中做目前想到两种， 一种是sql 操作， 另一种代码中做逻辑处理。

方式一 Java 操作

import lombok.Data;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author： bmxc
 * @date： 06/11/2024
 * @description： 把 student 对象中的 sortNum,实现行转成列， sortNum 范围是固定的，解决方式如下
 */
public class Test {
    @Data
    static class Student {
        private String id;
        private String name;
        private Integer sortNum;

    }

    @Data
    static class Student2 {
        private String id;
        private String name;
        private Integer sortNum1;
        private Integer sortNum2;
        private Integer sortNum3;
    }

    public static void main(String[] args) {
        List<Student> students = new ArrayList<>();
        Student student = new Student();
        student.setId("1");
        student.setName("test1");
        student.setSortNum(1);
        students.add(student);
        Student student1 = new Student();
        student1.setId("1");
        student1.setName("test2");
        student1.setSortNum(2);
        students.add(student1);
        Student student2 = new Student();
        student2.setId("2");
        student2.setName("test3");
        student2.setSortNum(3);
        students.add(student2);
        Student student3 = new Student();
        student3.setId("2");
        student3.setName("test4");
        student3.setSortNum(1);
        students.add(student3);

        // 诉求，把list 对应中的student的 sort_num 转成行，sort_num 是重复的，sort_num的值是一定的，假设是 3， 也即是行转列
        /**
         * 1. 把List转map
         * 2. 循环遍历mao 实现行转列
         */
        Map<String, List<Student>> studentHashMap = new HashMap<>();
        for (Student student4 : students) {
            if (!studentHashMap.containsKey(student4.getId())) {
                studentHashMap.put(student4.getId(), new ArrayList<>());
            }
            studentHashMap.get(student4.getId()).add(student);
        }

        // 构建新的对象，来平铺需要的每个对象中要展示的字段，能实现固定功能，就是比较死板。后续在优化
        for (Map.Entry<String, List<Student>> entry : studentHashMap.entrySet()) {
            List<Student> values = entry.getValue();
            Student2 stu = new Student2();
            if (values.size() > 0) {
                // 筛入基本值
                Student student4 = values.get(0);
                stu.setId(student4.getId());
                stu.setName(student4.getName());
            } else if (values.size() == 1) {
                Student stu0 = values.get(0);
                stu.setSortNum1(stu0.getSortNum());
            } else if (values.size() == 2) {
                Student stu0 = values.get(0);
                Student stu1 = values.get(1);
                stu.setSortNum1(stu0.getSortNum());
                stu.setSortNum2(stu1.sortNum);
            } else if (values.size() == 3) {
                Student stu0 = values.get(0);
                Student stu1 = values.get(1);
                Student stu2 = values.get(2);
                stu.setSortNum1(stu0.getSortNum());
                stu.setSortNum2(stu1.sortNum);
                stu.setSortNum3(stu2.sortNum);
            }
        }
    }

}

方式二 sql 操作

select 
t.sortNum
,t1.sortNum as sortNum1
,t2.sortNum as sortNum2
from student t
left join student t1
on t1.id=t.id
left join student t2
on t2.id=t.id