Column-to-Row Conversion

Version

Application Scenario

Convert the headers of multiple columns into field values to form a new column, thus achieving the aggregation and summary of various indicators to facilitate analysis and presentation.

For example, convert the headers Chinese, Maths, and English into field values to form a new Subject column, as shown in the following figure.

Function Description

The Column to Row operator in Data Transformation can be used to convert columns to rows, as shown in the following figure.

Scenario Description

You can download the sample data: Score.xls.

The details of the sample data are shown in the following figure.

You want to display the score of each subject as a separate record, instead of displaying each subject as a different column, as shown in the following figure.

Data Input

1. Create a scheduled task, drag the Data Transformation node to the design page, and click the node to configure it.

2. Drag the DB Table Input operator to the design page and extract data from the Score table, as shown in the following figure.

Click Data Preview to view the fetched data, as shown in the following figure.

Column-to-Row Conversion

1. Drag the Column to Row operator to the design page and connect it with the upstream DB Table Input operator.

2. Set Attribute Field Name to Subject to convert Chinese, Maths, English, and Chemistry into values of the Subject field. Set Value Field Name to Score to name the field containing scores of different subjects Score. Click Select Field to Be Converted to specify the column to be converted to records, as shown in the following figure.

Setting Item	Description
Attribute Field Name	It cannot be empty and should be within 30 characters. It can only consist of letters (from a to z, which is case-sensitive), digits (from 0 to 9), underscores (_), and Chinese characters. Spaces are not allowed. It must not be duplicated with the value of Value Field Name or any other field names in the original table except for the names of fields to be converted.
Value Filed Name	It cannot be empty and should be within 30 characters. It can only consist of letters (from a to z, which is case-sensitive), digits (from 0 to 9), underscores (_), and Chinese characters. Spaces are not allowed. It must not be duplicated with the value of Attribute Field Name or any other field names in the original table except for the names of fields to be converted.
Field Conversion	Note: Only fields with the same type and precision can be selected simultaneously. When no fields are selected, the Select All option will be grayed out. After selecting a field, the Select All option is selectable. You can click it to select all fields with the same type and precision. In the New Row Value column, deleting fields and modifying field names are supported. Field to Be Converted cannot be empty, and the values cannot be duplicated. New Row Value cannot be empty, and the value must be within 200 characters.

3. Click Data Preview to view the data after column-to-row conversion, as shown in the following figure.

Data Output

1. Drag a DB Table Output operator to the design page and connect it with the upstream Column to Row operator.

Output the processed data to a specified database, as shown in the following figure.

2. Select Write Data into Target Table Directly as Write Method.

Effect Display

1. Click Run. After the successful execution, the Log area at the bottom displays the node running status, as shown in the following figure.

2. The following figure shows the data in the target table.

Assume that null values or empty strings exist in the columns to be converted into rows, as shown in the following figure.

After column-to-row conversion, the values of the field of the corresponding row are null, as shown in the following figure.