* How to Combine Every Five Values into One Record in SQL

 

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

FILE

B.jpg

X.jpg

H.png

C.png

A.gif

G.pdf

Y.docx

U.jpeg

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

A

B

C

D

E

B.jpg

X.jpg

H.png

C.png

A.gif

G.pdf

Y.docx

U.jpeg

SQL written in MySQL:

SELECT MAX(CASE

                        WHEN RN % 5 = 0 THEN FILE

            ENDAS FILEA

            , MAX(CASE

                        WHEN RN % 5 = 1 THEN FILE

            ENDAS FILEB

            , MAX(CASE

                        WHEN RN % 5 = 2 THEN FILE

            ENDAS FILEC

            , MAX(CASE

                        WHEN RN % 5 = 3 THEN FILE

            ENDAS FILED

            , MAX(CASE

                        WHEN RN % 5 = 4 THEN FILE

            ENDAS 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.

Q & A Collection

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