DEV Community

Judy
Judy

Posted on

How to Combine Every Five Values into One Record #eg14

We have a database table TBL_FILE, which has data as follows:

Image description
We are trying to combine every five values into one new record, as shown below:

Image description
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)


Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
esproc_spl profile image
Judy

SPL open source address:github.com/SPLWare/esProc/stargazers

Collapse
 
martinbaun profile image
Martin Baun

Nice resource! Got a YT channel by any chance?

Collapse
 
esproc_spl profile image
Judy