CODEWITHSUNDEEP
mysqlconcatenation
Alex Shiganov
Alex Shiganov

Reputation: 134

MySQL: Rows concatenation

May be this is really a simple question, thanks in advance.

What I currently have:

+-----+---+---+---+---+
| sid | a | b | c | d |
+-----+---+---+---+---+
| 123 |   |   |   | 4 |
| 123 |   | 2 |   |   |
| 123 |   |   | 3 |   |
| 123 | 1 |   |   |   |
| 456 |   | 5 |   |   |
| 456 |   |   | 6 |   |
| 789 |   |   |   | 8 |
| 789 | 7 |   |   |   |
+-----+---+---+---+---+

What I am trying to get:

+-----+------+------+------+------+
| sid |  a   |  b   |  c   |  d   |
+-----+------+------+------+------+
| 123 | 1    | 2    | 3    | 4    |
| 456 |      | 5    | 6    |      |
| 789 | 7    |      |      | 8    |
+-----+------+------+------+------+

How such "rows concatenation" could be done in MySQL?

Upvotes: 2

Views: 41

Answers (1)

Arnaud Peralta
Arnaud Peralta

Reputation: 1305

You can do this with the MAX() aggregation function with a GROUP BY clause in your query.

SELECT sid, MAX(a), MAX(b), MAX(c), MAX(d)
FROM table
GROUP BY sid

I used MAX() because it will filter the NULL values with others values.

More explanation here : MySQL Documentation

Upvotes: 2

Related Questions