8.11 Place data in column groups
Placing data in column groups is to transform multiple rows of data arranged vertically to two or multiple column groups arranged horizontally.
Below is the world city population table:
| Continent | Country | City | Population |
|---|---|---|---|
| Africa | Egypt | Cairo | 6789479 |
| Asia | China | Shanghai | 24240000 |
| Europe | Britain | London | 7285000 |
| … | … | … | … |
The task is to list names and population of European and African cities having a population greater than 2 million in multiple column groups (rows in each column group are arranged by population in descending order), as shown below:
| Europe City | Population | Africa City | Population |
|---|---|---|---|
| Moscow | 8389200 | Cairo | 6789479 |
| London | 7285000 | Kinshasa | 5064000 |
| St Petersburg | 4694000 | Alexandria | 3328196 |
The approach is to create the target data structure and insert data into it.
SPL script:
| A | B | |
|---|---|---|
| 1 | =connect(“db”).query@x(“select * from World where Continent in(‘Europe’, ‘Africa’) and Population >= 2000000”) | |
| 2 | =A1.select(Continent:“Europe”) =A1.select(Continent:“Africa”) | |
| 3 | =create(‘Europe City’,Population,‘Africa City’, Population) | |
| 4 | =A3.paste(A2.(City),A2.(Population),B2.(City),B2.(Population)) |
A1 Connect to the database and retrieve data, getting records of European and African cities having a population above 2 million.
A2, B2 Select records of Europe and Africa respectively.
A3 Create an empty table sequence having the target structure.
A4 Use table sequence paste() function to paste sequences of values to corresponding columns.
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
