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

I have a select statement with rows as table_Schema

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

I need to merge the rows such that blogs with same row id are merged into a single row. I am trying to get either <author fname, author mname, author lname | coauthor fname coauthor lname> or a column as author co author Each blog can have multiple authors and co authors as an example Blog1 has Thomas Edison, Dan Mathre, Robert Cook as authors and Joe Randall as co author.

I tried

CONCAT_WS('|', group_concat(concat(ifnull(people.prefix, ' '), ' ' ,ifnull(people.first_name, ' '), ' ' ,ifnull(people.middle_name, ' '), ' ' ,ifnull(people.last_name, ' '), '|', ifnull(peopleData.prefix, ' '), ' ' ,ifnull(peopleData.first_name, ' '), ' ' ,ifnull(peopleData.middle_name, ' '), ' ' ,ifnull(peopleData.last_name, ' '))) ) 
             AS authors

which should ideally return author names and co author names separated by |||.

However there are multiple iterations here as in

Roy Thomas | Joe Shan, Roy Thomas | Kunal Shar, Thomas Edison | Joe Shan, Thomas Edison | Kunal Shar

I need only

If there's a lot of ambiguity please comment and I will clear it. Please dont downvote.

CodePudding user response：

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