DEV Community

Judy
Judy

Posted on

1 1 1 1 1

Row to column conversion involving uncertain columns #eg91

The query results of the Postgresql database return 6 columns, with the first column being the grouping column, the second column being the Value column within the group, and columns 3-6 being the Key columns within the group. Each record only has one Key column with a value, while the other Key columns are null. The position of the Key with a value for each record is uncertain.

Image description

Now we need to keep the grouping column unchanged, convert the details within the group from rows to columns, convert the values of non-null Key columns to new column names, and convert the values of Value column to new column values.

Image description

SPL code:

Image description

A1: Query the database through JDBC.

A2: Use the pivot function to convert this group of records from row to column. The first parameter is the grouping column, the second parameter is the column name of the Key, and the ifn function takes the first non-null member in the set. The third parameter is the Value column.

Open source SPL source address

Free Download

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more