8.12 Perform transposition while handling data type inconsistency
We have a database table FIRSTTBL. Part of its data is as follow:
| ID | PROPERTY | TYPE | STRING | INTEGER | DATETIME | BOLLEAN | XML |
|---|---|---|---|---|---|---|---|
| 1 | firstname | NVARCHAR | John | null | null | null | null |
| 1 | lastname | NVARCHAR | Doe | null | null | null | null |
| 1 | birthdate | DATETIME | null | null | 1986-09-16 | null | null |
| 1 | address | XML | null | null | null | null | eyJh… |
| 1 | flag | BIT | null | null | null | 1 | null |
| 1 | number | INT | null | 20 | null | null | null |
| 2 | … | … | … | … | … | … | … |
Every 6 records in the orginal table will be combined and transformed to one record in the following table. Below is part of the desired table:
| ID | FIRSTNAME | LASTANME | BIRTHDAY | ADDRESS | FLAG | NUMBER |
|---|---|---|---|---|---|---|
| 1 | John | Doe | 1986-09-16 | eyJh… | 1 | 20 |
| 2 | … | … | … | … | … | … |
The row-to-column transposition will use each group of PROPERTY field values (in every 6 records) as new columns, and take non-null values under STRING, INTEGER, DATETIME, BOLLEAN and XML columns (original data type of each column needs to be maintained).
SPL script:
| A | |
|---|---|
| 1 | =connect(“MSSQL”) |
| 2 | =A1.query@x(“SELECT * FROM FIRSTTBL”) |
| 3 | =A2.pivot(ID;PROPERTY,~.array().m(4:).ifn();“firstname”:“FIRSTNAME”,“lastname”:“LASTANME”,“birthdate”:“BIRTHDAY”,“address”:“ADDRESS”,“flag”:“FLAG”,“number”:“NUMBER”) |
A3 Transpose A2’s table by taking the first non-null values under the 4th column and columns after it as new column values and by converting each group of PROPERTY values into new column names in capital letters.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
