CODEWITHSUNDEEP
mysql
Rick
Rick

Reputation: 591

Cleaning up data in mysql

I have very large data set that's broken out a bit too far and I need to pull it back.

Say for example the data is a DVD box set. The initial catalogers added each episode as an entry (but ID'd it by the season). However, we only care about the individual DVDs from the box set.

I'd like to write a query that groups these entries by the ID numbers and concat the titles into a blob field.

That is...

Given a record set like this below...

| ID        | Title
+-----------+------------------------------------------------------------
| 1234      | Batman: Season 1, Episode 6: "Batman Is Riled"                                                                                                         | 
| 1234      | Batman: Season 1, Episode 7: "Instant Freeze"                                                                                                          | 
| 5678      | Batman: Season 2, Episode 9: "The Greastest Mother of Them All"                                                                                        | 
| 5678      | Batman: Season 2, Episode 7: "The Spell of Tut"                                                                                                        | 
| 5678      | Batman: Season 2, Episode 6: "Barbecued Batman?"                                                                                                       | 
| 5678      | Batman: Season 2, Episode 3: "Hot Off the Griddle"                                                                                                     |   
| 9012      | Batman: Season 3, Episode 24: "The Joker's Flying Saucer"                                                                                              | 
| 9012      | Batman: Season 3, Episode 25: "The Entracing Dr. Cassandra"                                                                                            | 
| 9012      | Batman: Season 3, Episode 26: "Minerva, Mayhem and Millionaires"

I'd like to convert this into a newer structure such as 

| ID        | Title  | Description
| 1234      | Batman |Batman: Season 1, Episode 6: "Batman Is Riled"; Batman: Season 1, Episode 7: "Instant Freeze"                                                                                                          | 
| 5678      | Batman |Batman: Season 2, Episode 9: "The Greastest Mother of Them; Batman: Season 2, Episode 7: "The Spell of Tut"; Batman: Season 2, Episode 6: "Barbecued Batman?"; Batman: Season 2, Episode 3: "Hot Off the Griddle"                                                                                                     |   
| 9012      | Batman |Batman: Season 3, Episode 24: "The Joker's Flying Saucer"; Batman: Season 3, Episode 25: "The Entracing Dr. Cassandra"; Batman: Season 3, Episode 26: "Minerva, Mayhem and Millionaires"

Is this possible in mysql or do I need to pull it out into a spread sheet?

Upvotes: 0

Views: 519

Answers (1)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

GROUP_CONCAT will do the trick

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE dvd
    (`ID` int, `Title` varchar(64))
;

INSERT INTO dvd
    (`ID`, `Title`)
VALUES
    (1234, 'Batman: Season 1, Episode 6: "Batman Is Riled"'),
    (1234, 'Batman: Season 1, Episode 7: "Instant Freeze"'),
    (5678, 'Batman: Season 2, Episode 9: "The Greastest Mother of Them All"'),
    (5678, 'Batman: Season 2, Episode 7: "The Spell of Tut"'),
    (5678, 'Batman: Season 2, Episode 6: "Barbecued Batman?"'),
    (5678, 'Batman: Season 2, Episode 3: "Hot Off the Griddle"'),
    (9012, 'Batman: Season 3, Episode 24: "The Joker''s Flying Saucer"'),
    (9012, 'Batman: Season 3, Episode 25: "The Entracing Dr. Cassandra"'),
    (9012, 'Batman: Season 3, Episode 26: "Minerva, Mayhem and Millionaires"')
;

Query 1:

select id, substring(title,1,locate(":",title)-1) title, 
group_concat(title SEPARATOR ";") as description
from dvd
group by ID

Results:

|   ID |  title |                                                                                                                                                                                                         description |
|------|--------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1234 | Batman |                                                                                                                        Batman: Season 1, Episode 6: "Batman Is Riled";Batman: Season 1, Episode 7: "Instant Freeze" |
| 5678 | Batman | Batman: Season 2, Episode 9: "The Greastest Mother of Them All";Batman: Season 2, Episode 7: "The Spell of Tut";Batman: Season 2, Episode 6: "Barbecued Batman?";Batman: Season 2, Episode 3: "Hot Off the Griddle" |
| 9012 | Batman |                              Batman: Season 3, Episode 24: "The Joker's Flying Saucer";Batman: Season 3, Episode 25: "The Entracing Dr. Cassandra";Batman: Season 3, Episode 26: "Minerva, Mayhem and Millionaires" |

Upvotes: 2

Related Questions