Clever Geek Handbook

MYSQL - concatenate rows with multiple duplicate values ​​and remove duplicates afterwards

So I have my database set up as one table. In this table, I have collected the original url and description (I am clearing the product description from multiple pages). Unfortunately I ended up with multiple lines in the database for the URL / source page if there are multiple paragraphs.

What I would like to do, if multiple lines with the same url, concatenate the description from each line and then remove the duplicate lines for that url.

My table is literally structured like this:

table             
+----+----------------------------+-------------+
| id | url                        | description |
+----+----------------------------+-------------+
|  1 | http://example.com/page-a  | paragraph 1 |
|  2 | http://example.com/page-a  | paragraph 2 |
|  3 | http://example.com/page-a  | paragraph 3 |
|  4 | http://example.com/page-b  | paragraph 1 |
|  5 | http://example.com/page-b  | paragraph 2 |
+----+----------------------------+-------------+

How I want it like:

table             
+----+----------------------------+-------------------------------------+
| id | url                        | description                         |
+----+----------------------------+-------------------------------------+
|  1 | http://example.com/page-a  | paragraph 1 paragraph 2 paragraph 3 |
|  2 | http://example.com/page-b  | paragraph 1 paragraph 2             |
+----+----------------------------+-------------------------------------+

I'm not really worried about the IDs being updated are correct, I just want to be able to combine lines where paragraphs should be in the same field as the same URLs, and then remove duplicates.

Any help would be greatly appreciated!

+3


source to share


3 answers


Easy to filter the table, just paste the result into a new table:



SELECT url, GROUP_CONCAT(description ORDER BY description SEPARATOR ' ') AS description
FROM `table`
GROUP BY url
+2


source


Create a new temp table, trim the original and re-insert the data:

create temporary table tempt as
    select (@rn := @rn + 1) as id, url,
           group_concat(description order by id separator ' ') as description
    from t cross join (select @rn := 0) params
    group by url 
    order by min(id);

-- Do lots of testing and checking here to be sure you have the data you want.

truncate table t;

insert into t(id, url, description)
    select id, url, description
    from tempt;


If id

already automatically added to the table, you don't need to provide a value for it.

+1


source


In SQL

SELECT MIN(id) as [ID],url, description= STUFF((SELECT '; ' 
+ ic.description FROM dbo.My_Table AS ic
WHERE ic.url= c.url
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'), 1, 2, '')
FROM dbo.My_Table AS c
GROUP BY url
ORDER BY url;
0


source






More articles:


All Articles