CODEWITHSUNDEEP
mysqlsqlexport-to-csvdenormalization
Mikey B
Mikey B

Reputation: 23

How to a denormalize repeating mysql data?

Hi I need to do some denormalizing on a MySQL table with repeating data.

My "Publications" table is currently in this format:

Publications Source Table   

|  title                   |       author
--------------------------------------------
|  my paper                |       michael
|  my paper                |       bill
|  my paper                |       jill
|  other paper             |       tom
|  other paper             |       amy
|  third paper             |       ben
|  third paper             |       sophie

I need to change it to this format:

Publications Destination Table

|  title                   |    author   |  author2   |  author 3
|-----------------------------------------------------------------
|  my paper                |    michael  |  bill      |  jill
|  other paper             |    tom      |  amy       |
|  third paper             |    ben      |  sophie    |

Now, just for your information I need to do this so I can eventually get a CSV file so the data can be exported from an old system into a new system that requires a CSV file in this format.

Also there are many other fields in the table and about 60,000 rows in the source table, but only about 15,000 unique titles. In the source table there is one row per author. In the destination, title will be a unique identifier. I need one row per unique publication title. Also I can calculate in advance what the most number of authors is on any one publication, if that makes the problem easier.

How can I do this in MySQL? Thanks

Upvotes: 1

Views: 777

Answers (2)

Stewart Macdonald
Stewart Macdonald

Reputation: 2142

If you don't actually want to alter the structure of the table, and instead just want to get the data out so you can import it into a new system, you could try the GROUP_CONCAT() function in mysql:

SELECT title, GROUP_CONCAT(author SEPARATOR "|") AS authors FROM publications GROUP BY title;

I've used the pipe as a separator as there's a good chance your titles will contain commas. If you want this to end up as a csv file, you could do a find-and-replace on the pipe character to turn it into whatever it needs to be (e.g., ", ").

Upvotes: 2

mba12
mba12

Reputation: 2792

My recommendation is that you actually normalize the table instead of adding new columns for supplemental authors. So your new table structure would look something like this:

Publications Source Table   

|  title_id         |       title
--------------------------------------------
|  1                |       my paper
|  2                |       other paper
|  3                |       third paper


|  title_id      |       author
--------------------------------------------
|  1             |       michael
|  1             |       bill
|  1             |       jill
|  2             |       tom
|  2             |       amy
|  3             |       ben
|  3             |       sophie

Upvotes: -1

Related Questions