How to merge the result from the same column-CodePudding

So the data i want to combine column C & D together because they have the same ID as column A

Column A	Column B	Column C	Column D
A	A	B	B
A	A	C	C

And i want to look like this

Column A	Column B	Column C	Column D
A	A	B C	B C

CodePudding user response：

Try to use aggregation query like below (Test on MySQL 8.0):

select Column A, Column B, concat(Column C, Column D) from table group by Column A, Column B;

In MySQL 8.0, this query sql will not work as ERROR 1055 (42000): this is incompatible with sql_mode=only_full_group_by. So set sql_mode=‘’ for current session.

CodePudding user response：

You can use GROUP_CONCAT and REPLACE to get the desired result set. Please check below.

create table t1 (col1 varchar(10), col2 varchar(10), col3 varchar(10), col4 varchar(10)); 
insert into t1 values ('A', 'A', 'B', 'B');
insert into t1 values ('A', 'A', 'C', 'C');
insert into t1 values ('B', 'X', 'J', 'G');
insert into t1 values ('B', 'Y', 'K', 'L');

SELECT col1, 
       REPLACE(GROUP_CONCAT(col3), ',', ' ') as x, 
       REPLACE(GROUP_CONCAT(col4), ',', ' ') as y
FROM t1
group by col1;

Result:

A   B C    B C
B   J K    G L