CODEWITHSUNDEEP
mysqlsqlmysql-workbench
user3882752
user3882752

Reputation: 273

Concatenate second field with first field in mysql query

I have a table with the below information. Keep in mind that the columns ID,Year,ID2 together creates a key. 

+----+------+-------+------+------+
| ID | Year | ID2   |Year1 |Year2 |
+----+------+-------+------+------+
|  1 | 1    | 12    |4     |      |
|  1 | 1    | 13    |6     |      |
|  1 | 1    | 22    |7     |      |
|  1 | 2    | 12    |      |4     |
|  1 | 2    | 15    |      |5     |
|  1 | 2    | 17    |      |4     |
|  1 | 2    | 25    |      |5     |
+----+------+-------+------+------+

I would like to concatenate the Year1 field all the way down when applicable and the same for Year2 field such as it will not show duplicates if a number is already there. I want the query to present the result below.

+----+------+-------+------+------+
| ID | Year | ID2   |Year1 |Year2 |
+----+------+-------+------+------+
|  1 | 1    | 12    |4     |      |
|  1 | 1    | 13    |4,6   |      |
|  1 | 1    | 22    |4,6,7 |      |
|  1 | 2    | 12    |      |4     |
|  1 | 2    | 15    |      |4,5   |
|  1 | 2    | 17    |      |4,5   |
|  1 | 2    | 25    |      |4,5   |
+----+------+-------+------+------+

Thanks in advance!

Upvotes: 1

Views: 64

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

You can do a self-join on the same id and year and a smaller id2 use group_concat to concatenate the corresponding year1 and year2 columns

select t1.id, t1.year, t1.id2,
    group_concat(distinct t2.year1 order by t2.year) year1,
    group_concat(distinct t2.year2 order by t2.year2) year2
from mytable t1
join mytable t2 on t2.id = t1.id 
    and t2.year = t1.year 
    and t2.id2 <= t1.id2
group by t1.id, t1.year, t1.id2
order by t1.id, t1.year, t1.id2

Upvotes: 1

Related Questions