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.
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)
filea | fileb | filec | filed | filee :---- | :----- | :----- | :---- | :---- B.jpg | X.jpg | H.png | C.png | A.gif G.pdf | Y.docx | U.jpeg | null | null