# Comparison of SQL & SPL: Static Transposition

A static transposition can define the data structure after data is transposed beforehand and the structure does not change accordingly as data is changed.

**1. Row-to-column transposition**

As the name shows, row-to-column transposition aims to convert row values into column names to transfer rows to columns. In practice, PIVOT is often used after the grouping and aggregation, which gets unique values from the row values under the to-be-transposed columns and then arrange the row values into column names. The role of PIVOT is to group and put the aggregates in a specific column into multiple columns for an intuitive representation.

【Example 1】Based on the following scores table, find the highest score of each subject in each class and present the results in columns. Below is part of the source table:

CLASS | STUDENTID | SUBJECT | SCORE |

1 | 1 | English | 84 |

1 | 1 | Math | 77 |

1 | 1 | PE | 69 |

1 | 2 | English | 81 |

1 | 2 | Math | 80 |

… | … | … | … |

Below is the result of expected layout:

CLASS | MAX_MATH | MAX_ENGLISH | MAX_PE |

1 | 97 | 96 | 97 |

2 | 97 | 96 | 97 |

… | … | … | … |

**SQL solution:**

SQL PIVOT is used to achieve row-to-column transposition and column-to-row transposition. But only the relatively new versions of certain database products support this method. ORACLE, for instance, supports it from the 11g version, and some databases, such as MYSQL, still uses the subquery to do the grouping and aggregation and then a left join to achieve the transposition and have not given any support to it. In this case, we use ORACLE 11g to write the SQL query:

SELECT * FROM ( SELECT CLASS, SUBJECT, SCORE FROM SCORES ) PIVOT ( MAX(SCORE) FOR SUBJECT IN ( 'Math' AS MAX_MATH, 'English' AS MAX_ENGLISH, 'PE' AS MAX_PE ) )

**SPL solution:**

SPL offers A.pivot() function to perform transposition, row-to-column by default.

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 Scores table from the source file.**A2**: Group A1’s table by class and subject and calculate the highest score of each subject in each class.**A3**: A.pivot() function transposes the row-wise highest scores into column names.

SPL supports retrieving a data table from the database, too. A1 in the above script can be modified as:

A | |

1 | =connect("db").query("SELECT * FROM SCORES") |

**2. ****C****olumn-to-row transposition**

Contrary to row-to-column transposition, the column-to-row transposition converts each field into a new row, where the new field value comes from the original field name or alias, and transforms the original field values into field values of a new field.

【Example 2】Based on the following Olympic medal table, generate a new table recording information for each type of medal. Below is part of the source table:

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 |

… | … | … | … | … |

Below is the result of expected layout:

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 |

… | … | … | … |

**SQLsolution:**

SPL uses UNPIVOT function to perform a column-to-row transposition:

SELECT * FROM OLYMPIC UNPIVOT ( MEDALS FOR MEDAL_TYPE IN ( GOLD,SILVER,COPPER ) )

**SPL****solution:**

SPL A.pivot() function works with @r option to perform a column-to-row transposition:

A | |

1 | =T("Olympic.txt") |

2 | =A1.pivot@r(GAME,NATION; MEDAL_TYPE, MEDALS; GOLD, SILVER, COPPER) |

**A1**: Import Olympic medal table.**A2**: A.pivot@r() function transposes columns GOLD, SILVER and COPPER into new rows.

**3. Scenarios containing both types of transpositions**

【Example 3】Based on the following channel-based sales table, generate a new table storing information by date. Below is part of the source table:

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 |

… | … | … | … |

Below is the result of expected layout:

CATEGORY | 1 | 2 | 3 | … |

ONLINE | 2440 | 1863.4 | 1813.0 | … |

STORE | 3746.2 | 448.0 | 624.8 | … |

**SQL solution:**

We need to perform both row-to-column transposition and column-to-row transposition to get this done. First, we perform column-to-row transposition to transform channel types into values under CATEGORY field:

YEAR | MONTH | CATEGORY | AMOUNT |

2020 | 1 | ONLINE | 2440 |

2020 | 1 | STORE | 3746.2 |

2020 | 2 | ONLINE | 1863.4 |

2020 | 2 | STORE | 448.0 |

… | … | … | … |

Then we perform row-to-column transposition to convert MONTH values into column names. The complete SQL query is as follows:

SELECT * FROM ( SELECT * FROM MONTH_SALES UNPIVOT ( AMOUNT FOR CATEGORY IN ( "ONLINE",STORE ) ) WHERE YEAR=2020 ) PIVOT ( MAX(AMOUNT) FOR MONTH IN ( 1 AS "1",2 AS "2",2 AS "3", 4 AS "4",5 AS "5",6 AS "6", 7 AS "7",8 AS "8",9 AS "9", 10 AS "10",11 AS "11",12 AS "12" ) )

SPL does not support using a non-constant expression as PIVOT/UNPIVOT value, so all months need to be enumerated for the row-to-column transposition.

**SPL solution:**

According to the natural logic, SPL handles the task using A.pivot@r() and A.pivot() respectively for column-to-row transposition and row-to-column transposition:

A | |

1 | =T("MonthSales.csv").select(YEAR:2020) |

2 | =A1.pivot@r(YEAR,MONTH; CATEGORY, AMOUNT) |

3 | =A2.pivot(CATEGORY; MONTH, AMOUNT) |

**A1**: Import MonthSales table and select records of the year 2020.**A2**: A.pivot@r() performs column-to-row transposition to convert channel types into values of CATEGORY field.**A3**: A.pivot () performs row-to-column transposition to transform MONTH field values into column names.

Both SQL and SPL handle simple transposition scenarios well. The issue is that the real-world situations cannot always dealt with using the mode of grouping & aggregation plus PIVOT. In the subsequent essays in our transposition series, we will introduce how the two languages handle complicated static transpositions and dynamic transpositions.