I have a table column like following
x
Name
a
b
c
d
e
f
Now I want to Divide this column into 3 columns like as
Name1 | Name2 | Name3
a | c | f
b | d | e
How will the SQL Query for this?
Advertisement
Answer
On MySQL 8+, you could use ROW_NUMBER
for this purpose:
WITH cte AS (
SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) - 1 rn
FROM yourTable
)
SELECT
MAX(CASE WHEN FLOOR(rn / 2) = 0 THEN Name END) AS Name1,
MAX(CASE WHEN FLOOR(rn / 2) = 1 THEN Name END) AS Name1,
MAX(CASE WHEN FLOOR(rn / 2) = 2 THEN Name END) AS Name3
FROM cte
GROUP BY
rn % 2
ORDER BY
rn % 2;
Demo
This approach has an advantage over a union in that it can easily be extended to support more rows and columns as you need.