Row-to-column transposition is often needed in handling MongoDB data in real-world businesses. We have a collection scores that records student scores of English, Math and PE. The computing task is to display scores of subjects in different columns.

The expected result:

As MongoDB doesn’t offer related API to handle data by group, it’s roundabout to get this done.

esProc SPL has pivot function to handle this directly.
Download esProc installation package HERE.

Directions:

1. Write SPL script scores.dfx in esProc:

  2. Execute the script to return the final result:

pivot()function group rows by the given CLASS field and STUDENTID field, uses SUBJECT values as new columns and populates each of score values in corresponding record under the right field. In this way the row-to-column transposition is done right. On the contrary, pivot@r() function performs column-to-row transposition.

esProc provides JDBC interface to let you integrate the script into a Java program conveniently:
public static void doWork() {

    Connection con = null;
    java.sql.Statement st;
   
    try{
        Class.forName("com.esproc.jdbc.InternalDriver");
        con = DriverManager.getConnection("jdbc:esproc:local://"); 
        // Call script scores.dfx
        st=con.createStatement(); 
        ResultSet rst = st.executeQuery("call scores");
        System.out.println(rst);
    } catch(Exception e){
        System.out.println(e);
    } finally{
        // Close database connection
        if (con!= null) {
            try {
                con.close();
            } catch(Exception e) {
                System.out.println(e);
            }
        }  
    }
}

Read How to Call an SPL Script in Java to learn more about integration of esProc SPL script into a Java program.