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:

SPL supports dynamic data structure and order-based calculations directly. It is convenient for it to append values in turn to a table.

Q & A Collection

https://stackoverflow.com/questions/64133340/how-to-convert-mysql-rows-to-a-column-of-5