How to Group MySQL Rows with Same Column Value into One Row

Can I concatenate multiple MySQL rows into one field?

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

How to group mysql rows with same column value into one row?

Use GROUP_CONCAT() like this:

 SELECT k.id, GROUP_CONCAT(d.value)
  FROM keywords AS k
  INNER JOIN data as d ON k.id = d.id
  GROUP BY k.id

Also, you may need to do ORDER BY d.name to get exact order of values as you want. Like this:

 SELECT k.id, GROUP_CONCAT(d.value ORDER BY d.name separator ' ')
  FROM keywords AS k
  INNER JOIN data as d ON k.id = d.id
  GROUP BY k.id

MYSQL how to merge rows with same field id into a single row

GROUP_CONCAT supports DISTINCT and SEPARATOR``

CREATE TABLE table1 (
  `rowid` VARCHAR(139),
  `title` VARCHAR(139),
  `author_f_name` VARCHAR(139),
  `author_m_name` VARCHAR(139),
  `author_l_name` VARCHAR(139),
  `coauthor_first_name` VARCHAR(139),
  `coauthor_middle_name` VARCHAR(139),
  `coauthor_last_name` VARCHAR(139)
);

INSERT INTO table1
  (`rowid`, `title`, `author_f_name`, `author_m_name`, `author_l_name`, `coauthor_first_name`, `coauthor_middle_name`, `coauthor_last_name`)
VALUES
  ('1.',   'Blog Title.',       'Roy',                  NULL,                   'Thomas.',               'Joe',                   'Shann',               'Mathews'),
  ('1.',   'Blog Title.',       'Thomas',                  'NULL',              'Edison',            'Kunal',               NULL,                       'Shar');

SELECT 
`rowid`
, GROUP_CONCAT(DISTINCT `title`  SEPARATOR ' |||') tilte
, GROUP_CONCAT(DISTINCT `author_f_name` SEPARATOR ' |||') author_f_name
, GROUP_CONCAT(DISTINCT `author_m_name` SEPARATOR ' |||') author_m_name
, GROUP_CONCAT(DISTINCT `author_l_name` SEPARATOR ' |||') author_l_name
, GROUP_CONCAT(DISTINCT `coauthor_first_name` SEPARATOR ' |||') coauthor_first_name
, GROUP_CONCAT(DISTINCT `coauthor_middle_name` SEPARATOR ' |||') coauthor_middle_name
, GROUP_CONCAT(DISTINCT `coauthor_last_name` SEPARATOR ' |||') coauthor_last_name
FROM table1
GROUP BY `rowid`

rowid | tilte       | author_f_name | author_m_name | author_l_name     | coauthor_first_name | coauthor_middle_name | coauthor_last_name
:---- | :---------- | :------------ | :------------ | :---------------- | :------------------ | :------------------- | :-----------------
1.    | Blog Title. | Roy |||Thomas | NULL          | Edison |||Thomas. | Joe |||Kunal        | Shann                | Mathews |||Shar   

db<>fiddle here

SELECT 
`rowid`
, GROUP_CONCAT(DISTINCT `title`  SEPARATOR ' |||') tilte
, GROUP_CONCAT(DISTINCT CONCAT(`author_f_name`,' ',COALESCE(`author_m_name`,''),' ',`author_l_name`) SEPARATOR ' |||') author_full_name
, GROUP_CONCAT(DISTINCT CONCAT(`coauthor_first_name`,' ',COALESCE(`coauthor_middle_name`,''),' ',`coauthor_last_name`) SEPARATOR ' |||') coauthor_full_name
FROM table1
GROUP BY `rowid`

rowid | tilte       | author_full_name                   | coauthor_full_name              
:---- | :---------- | :--------------------------------- | :-------------------------------
1.    | Blog Title. | Roy  Thomas. |||Thomas NULL Edison | Joe Shann Mathews |||Kunal  Shar

db<>fiddle here

Merge Multiple Rows in One Row Mysql

If you are OK with having a comma-separated list of attribute IDs and values per each sku, then you could use GROUP_CONCAT:

SELECT sku,
       GROUP_CONCAT(attribute_id)    AS attribute_id,
       GROUP_CONCAT(attribute_value) AS attribute_value
FROM yourTable
GROUP BY sku

GROUP BY and get the columns values into single row column

GROUP BY is designed to reduce the number of rows and to enable aggregations for those rows. In your case the following query, without group by produces:

SELECT room_name, subject_name
FROM classroom 
LEFT JOIN subject ON classroom.subject_id = subject.subject_id
LEFT JOIN room ON classroom.room_id = room.room_id 

+-----------+--------------+
| room_name | subject_name |
+-----------+--------------+
| sunflower | english      |
| sunflower | math         |
| sunflower | science      |
+-----------+--------------+

So there isn't much point in using GROUP BY, but if we do, this is the result:

SELECT room_name, subject_name
FROM classroom 
LEFT JOIN subject ON classroom.subject_id = subject.subject_id
LEFT JOIN room ON classroom.room_id = room.room_id 
GROUP BY room_name, subject_name

+-----------+--------------+
| room_name | subject_name |
+-----------+--------------+
| sunflower | english      |
| sunflower | math         |
| sunflower | science      |
+-----------+--------------+

Note that the values are presented in both columns for every row. This IS both expected AND DESIRED.

GROUP BY is not a "presentation" tool that will suppress output of values in the first column, that is something that a "report tool" or "presentation layer" would be expected to handle.

demo

---

Related Topics