SQL：按一些ID合并两行并保留值

Question

I'm facing a relatively easy problem but I can't manage to wrap my head around a solution. Keep in mind I'm using PHP with the Laravel framework if it can makes things any easier.

I have a table filled with data like so :

ID | TRANSACTION_ID | BEACON_TYPE
---+----------------+---------------
 1 |       1        |  "abc"
 2 |       2        |  "def"
 3 |       2        |  "xyz"

and I would like to group them by transaction ID and to keep the beacon type inside the data like so:

ID | TRANSACTION_ID | BEACON_TYPE
---+----------------+---------------
 1 |       1        |  "abc"
 2 |       2        |  "def", "xyz"

I've tried using group by with no avail. Any ideas or hints on how to accomplish that? As I said earlier there might be some way to do it with Laravel Eloquent.

Answer 1

Assuming you're using MySQL, the function you're looking for is GROUP_CONCAT(). Using Eloquent, it would look something like this:

$transactions = DB::table('tableName')
                  ->select('TRANSACTION_ID', DB::raw('GROUP_CONCAT(BEACON_TYPE SEPARATOR ', ') as BEACON_TYPE'))
                  ->groupBy('TRANSACTION_ID')
                  ->get();

Note, if you want to change the separator, you just need to edit the ', ' to something else. The default separator is ','.