How to convert MySQL rows to a column of 5

Question

I'm new to MySQL. I have a datatable and I'll like to convert it to a row of 5.

From SELECT File FROM tbl_file. I have

+--------+
| File   |  
+--------+
| B.jpg  | 
+--------+
| X.jpg  | 
+--------+
| H.png  | 
+--------+
| C.png  | 
+--------+
| A.gif  |
+--------+
| G.pdf  | 
+--------+
| Y.docx | 
+--------+
| U.jpeg | 
+--------+

Here's what I need when the rows are rotated to 5 columns.

+-------+--------+--------+-------+-------+
| A     | B      | C      | D     | E     |
+-------+--------+--------+-------+-------+
| B.jpg | X.jpg  | H.png  | C.png | A.gif |
+-------+--------+--------+-------+-------+
| G.pdf | Y.docx | U.jpeg |       |       |
+-------+--------+--------+-------+-------+

When the first row is filled to it's limit which is 5, then the next row continues to be filled.

This is what I've tried:

SELECT GROUP_CONCAT(File) FROM tbl_file GROUP BY id; I'm not getting what I intended to get.

Thanks a lot.

@strawberry I already have lots of loops in my app code. Adding this would make it get slower. Since the DB is faster, I considered run it directly and returning a table. — Tyler Wayne, Sep 30 '20 at 08:11

score 2 · Accepted Answer · answered Sep 30 '20 at 08:05

It is a bit ugly... but possible. You can use window functions - but you need a column that defines the ordering of the rows, I assumed id.

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 rn
    from mytable t
) t
group by floor(rn / 5)

Demo on DB Fiddle:

filea | fileb  | filec  | filed | filee
:---- | :----- | :----- | :---- | :----
B.jpg | X.jpg  | H.png  | C.png | A.gif
G.pdf | Y.docx | U.jpeg | null  | null

How to convert MySQL rows to a column of 5

1 Answers1