Concatenate rows on the basis of boolean values in another column with MySQL

To concatenate rows on the basis of boolean value in another column, use GROUP_CONCAT(). Let us first create a table. Here, we have set one of the columns “isValidUser” as BOOLEAN −

mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserMessage varchar(100),    isValidUser boolean ); Query OK, 0 rows affected (0.63 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(UserMessage,isValidUser) values('Hi',true); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable(UserMessage,isValidUser) values('Hello',false); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable(UserMessage,isValidUser) values('Good',true); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(UserMessage,isValidUser) values('Awesome !!!!!',true); Query OK, 1 row affected (0.09 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----+---------------+-------------+ | Id | UserMessage   | isValidUser | +----+---------------+-------------+ |  1 | Hi            |           1 | |  2 | Hello         |           0 | |  3 | Good          | 1 | |  4 | Awesome !!!!! | 1 | +----+---------------+-------------+ 4 rows in set (0.03 sec)

Following is the query to concatenate rows on the basis of boolean values in another column. Here, we are concatenating corresponding records for boolean 1 value and same for boolean 0 −

mysql> select isValidUser,group_concat(UserMessage) from DemoTable group by isValidUser;

This will produce the following output −

+-------------+---------------------------+ | isValidUser | group_concat(UserMessage) | +-------------+---------------------------+ | 0 | Hello | | 1 | Hi,Good,Awesome !!!!! | +-------------+---------------------------+ 2 rows in set (0.07 sec)