We have a database table TBL_FILE, which has data as follows:
We are trying to combine every five values into one new record, as shown below:
SQL written in MySQL:
SELECT MAX(CASE
WHEN RN % 5 = 0 THEN FILE
END) AS FILEA
, MAX(CASE
WHEN RN % 5 = 1 THEN FILE
END) AS FILEB
, MAX(CASE
WHEN RN % 5 = 2 THEN FILE
END) AS FILEC
, MAX(CASE
WHEN RN % 5 = 3 THEN FILE
END) AS FILED
, MAX(CASE
WHEN RN % 5 = 4 THEN FILE
END) AS FILEE
FROM (
SELECT T.*, ROW_NUMBER() OVER (ORDER BY ID) - 1 AS RN
FROM TBL_FILE T
) T
GROUP BY FLOOR(RN / 5)
A rather simple task. We just need to create a 5-column empty table, and insert values to the table by row and column. It is complicated to write the process in SQL. The language will create an extra id column to maintain the original order, invent indexes in a complicated way, and then distribute values to five columns according to the indexes. Coding will be even harder with dynamic columns.
It will be easy to do the task using the open-source esProc SPL:
| A |
|
| 1 |
=connect("mysql") |
| 2 |
=A1.query@xi("SELECT * FROM TBL_FILE") |
| 3 |
=create(A,B,C,D,E).record(A2) |
SPL supports dynamic data structure and order-based calculations directly. It is convenient for it to append values in turn to a table.
Top comments (3)
SPL open source address:github.com/SPLWare/esProc/stargazers
Nice resource! Got a YT channel by any chance?
youtube.com/channel/UC0drhdMyBpzgk...