MySQL (mariaDB) I want to aggregate my Select results, where all columns but one are equal and the none equal column is CONCAT_WS into a single value

Question

I have several occurrences of rows returned from a left join query with all columns being equal except one. These columns will be in sequential order.

The column that is not equal I want to aggregate into a single value using CONCAT_WS and only have one row in the output result for the rows which are equal except for the one I wish to aggregate.

I can do this with a PHP script, but I wondered if it was possible with an SQL statement

I am using mariaDB as my mySQL server

example

col1    col2    col3    col4    col5

aa      bb      cc      dog     dd
aa      bb      cc      cat     dd
aa      bb      cc      pig     dd
aa      bb      cc      monkey  dd
aa      bb      cc      bear    dd

I want to get a single result for those records as follows. From the select queries there will be many blocks of these styled records and also single unique value rows.

col1    col2    col3    col4                            col5
aa      bb      cc      dog,cat,pig,monkey,bear         dd

using CONACAT_WS(",", rec1.col4, rec2.col4, rec3.col4,rec4.col4,rec5.col4)

Is the above possible with just SQL ?

It seems like a likely scenario, that other people might have wanted to do.

There may be a technical term for what I want to do, but I do not know it.

Thanks

mcl

Answer 1

You should try adding "group by col1" this will aggregate in a single row every record having the same value in that column