how to combine multiple rows into one row?

My table:

id  val
--------
 a   1   
 a   2
 a   3
 b   7
 b   8
 b   9
 b   10

What i want to get is:

id  val1  val2  val3  val4
--------------------------
a    1     2     3    null
b    7     8     9     10

Is there any simple way for this?

Answer

If you are using MySQL 8+, then ROW_NUMBER combined with pivoting logic provides one way:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY val) rn
    FROM yourTable
)

SELECT
    id,
    MAX(CASE WHEN rn = 1 THEN val END) AS val1,
    MAX(CASE WHEN rn = 2 THEN val END) AS val2,
    MAX(CASE WHEN rn = 3 THEN val END) AS val3,
    MAX(CASE WHEN rn = 4 THEN val END) AS val4
FROM cte
GROUP BY
    id;