Row-to-Column Conversion

Version Description

Application Scenario

The Row to Column operator can convert a one-dimensional table into a two-dimensional table.

• A one-dimensional table is also known as a pipeline table, which has fixed column names. You just need to add data row by row.

• A two-dimensional table is a relational table in which the values in data areas are determined by both rows and columns. It is suitable for displaying analysis results as it is more in line with your reading habits and contains denser information.

Function Description

The Row to Column operator in Data Transformation can be used to convert a column of data originally scattered across multiple rows to multiple columns of data in a single row.

You can download the sample data: transform.xls

This document introduces how to convert the one-dimensional table in the sample data to a two-dimensional table.

Creating a Scheduled Task

1. Create a scheduled task and drag the Data Transformation node to the design page, as shown in the following figure.

2. Click the Data Transformation node to configure it.

Inputting the Data

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

Configuring Row-to-Column Conversion

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

• Set Field to Be Converted to Subject. The field values to be converted to column will be fetched automatically. The field values to be converted to column can be added manually. The field name must not be empty or be duplicated with the existing names.

• Set Value Field to Score. Target Field will be generated automatically.

2. Field names in Target Field can be modified. The requirements are:

• must not be empty

• must be within 30 characters

• can only consist of letters (from a to z, which is case-sensitive), digits (from 0 to 9), underscores (_), and dashes(-)

• must not include spaces

• must not be duplicated with the names of the fields to be converted, the value fields, the grouping fields, the unselected fields, and the added fields

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

Outputting the Data

1. Drag a DB Table Output operator to the design page and configure it to make the processed data output to a specified database, as shown in the following figure.

2. Click Save.

Displaying the Results

1. Click Run, as shown in the following figure.

After the task is successfully executed, you can see the converted data in the database, as shown in the following figure.