SPL：Static Transposition

The transposition function is commonly presented in front-end such as reports, which transposes the queried data into a specific display layout. For example, row-to-column transposition, column-to-row transposition, and more complex dynamic transposition, etc. And static transposition means that the structure of the transposed table is determined in advance, and the target structure will not change dynamically according to the data.

1. Row-to-column transposition

As the name implies, this functionality is used to perform the transposition from row to column, that is, taking the values in the rows as the names of columns. In practice, the application of row-to-column transposition usually follows the grouping and aggregation operations. Through grouping, we process the data of rows in the column to be transposed into distinct values, and then display the values of rows as the names of the columns. In fact, the function of row-to-column transposition is to subdivide the aggregation result of one column into a number of more specific column aggregation results for a more intuitive display effect.

[e.g.1] According to the students’ grade table, query the highest grades of each subject in each class and display them by column. And some of the data are as follows:

CLASS	STUDENTID	SUBJECT	SCORE
1	1	English	84
1	1	Math	77
1	1	PE	69
1	2	English	81
1	2	Math	80
…	…	…	…

The results are expected to be displayed in the following form:

CLASS	MAX_MATH	MAX_ENGLISH	MAX_PE
1	97	96	97
2	97	96	97
…	…	…	…

The A.pivot() function is provided in SPL for transposition, and the default is row-to-column transposition.

The SPL script is:

	A
1	=T("Scores.csv")
2	=A1.groups(CLASS,SUBJECT; max(SCORE):MAX_SCORE)
3	=A2.pivot(CLASS; SUBJECT, MAX_SCORE; "Math":"MAX_MATH", "English":"MAX_ENGLISH", "PE":"MAX_PE")

A1: import the students’ grade table from the file.

A2: group and aggregate the records to get the highest grade of each subject in each class.

A3: use the A.pivot() function to transpose the highest grade of each subject in each class from row to column.

2. Column-to-row transposition

In the opposite of row-to-column transposition, column-to-row transposition needs to generate a new row for each field to be transposed, and the field name or corresponding name is converted to the value of the new field, and the original field value is converted to the value of another new field.

[e.g.2] Generate a list of each medal based on the Olympic medal table. And some of the data are as follows:

Game	Nation	Gold	Silver	Copper
30	USA	46	29	29
30	China	38	27	23
30	UK	29	17	19
30	Russia	24	26	32
30	Korea	13	8	7
…	…	…	…	…

The results are expected to be displayed in the following form:

GAME	NATION	MEDAL_TYPE	MEDALS
30	USA	GOLD	46
30	USA	SILVER	29
30	USA	COPPER	29
30	China	GOLD	38
30	China	SILVER	27
30	China	COPPER	23
…	…	…	…

The @r option of the A.pivot() function is used to transpose columns to rows.

The SPL script is:

	A
1	=T("Olympic.txt")
2	=A1.pivot@r(GAME,NATION; MEDAL_TYPE, MEDALS; GOLD, SILVER, COPPER)

A1: import the Olympic medal table.

A2: use the A.pivot@r() function to transpose fields GOLD, SILVER, COPPER from columns to rows.

3. The mutual transposition between row and column

Sometimes we need to use the value of a row as the column name, and convert the column name to a field value.

[e.g.3] The sales table classified by sales channel is recorded according to the year and month. And some of the data are as follows:

YEAR	MONTH	ONLINE	STORE
2020	1	2440	3746.2
2020	2	1863.4	448.0
2020	3	1813.0	624.8
2020	4	670.8	2464.8
2020	5	3730.0	724.5
…	…	…	…

To query the sales amount of each channel in every month, and the results are expected to be displayed in the following form:

CATEGORY	1	2	3	…
ONLINE	2440	1863.4	1813.0	…
STORE	3746.2	448.0	624.8	…

Logically, we first use the A.pivot@r() function to perform column-to-row transposition and then use the A.pivot() function to transpose rows to columns.

The SPL script is:

	A
1	=T("MonthSales.csv").select(YEAR:2020)
2	=A1.pivot@r(YEAR,MONTH; CATEGORY, AMOUNT)
3	=A2.pivot(CATEGORY; MONTH, AMOUNT)

A1: import the sales table, and select the records of year 2020.

A2: use the A.pivot@r() function to perform column-to-row transposition, converting the channel type of data to the field value of CATAGORY. The results are as follows:

YEAR	MONTH	CATEGORY	AMOUNT
2020	1	ONLINE	2440
2020	1	STORE	3746.2
2020	2	ONLINE	1863.4
2020	2	STORE	448.0
2020	3	ONLINE	1813.0
2020	3	STORE	624.8
…	…	…	…

A3: use the A.pivot() function to transpose the values of the month field to columns.

MonthSales.csv

Olympic.txt

Scores.csv