DEV Community

Judy
Judy

Posted on

Split values of a field in a database table and transform it to new fields #eg37

Below is a table queried from BigQuery database. Its 4th field has comma-separated strings.

Image description
Use Java to do this: Retain the first 3 fields, split values the 4th field, make the distinct values names of the fields from 4th to Nth, write 1 or 0 under each new field to record whether they are originally included in the current row or not, and save result as a new file. Below is the expected result:

Image description
Write SPL code as follows:

Image description
A1: Query the database through JDBC.

A2: Split up each value of the 4th field and generate a record for each distinct value; ~ is the current item.

A3: Perform row-to-column transposition – retain the first 3 columns, transform values of the 4th column to the new field names, and write 1 under each new field if they are included in the current row in the original table and 0 if they are not.

A4: Write result to a csv file.

This is one of the problems on StackOverflow. You can click on it to see that the conventional solution is quite complicated, but the SPL approach is really simple and efficient.

SPL open source address

Top comments (0)