Condensing Multiple Rows to a Single Summary Row

Question

I'm new to MySQL and am in need of some help. I have a table where each row represents an item for an order. These items are located in a specific area, as seen below:

Order Number            Area
EEN0103944              305
EEN0103945              305
EEN0103945              305
EEN0103902              104
EEN0103902              111
EEN0103945              123
EEN0103945              005
EEN0103945              002

I'm trying to create a table in MySQL that condenses all the information into one row, making orders the primary key, like below:

Order Number             Area
EEN0103945               305
EEN0103945               104, 111, 123
EEN0103945               005, 002

Don't. This is a violation of First Normal Form (the absolute bare minimum of good database design) and will make your life so much harder. — mypetlion, Mar 22 '18 at 22:23
Detailed answer to similar question: https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns — rgucluer, Mar 22 '18 at 22:28
Why would you want to do this? Take perfectly good, atomised data... and ruin it. — Strawberry, Mar 22 '18 at 22:37

score 1 · Answer 1 · answered Mar 22 '18 at 22:25

1

You are looking for group_concat() with the distinct option:

select order_number, group_concat(distinct area separator ', ') as areas
from t
group by order_number

answered Mar 22 '18 at 22:25

Gordon Linoff

1,242,037
58
646
786

... but pull this as a resultset, Don't create database tables like this. This topic is covered in Chapter 2 (Jaywalking) of Bill Karwin's excellent book "SQL Antipatterns: Avoiding the Pitfalls of Database Programming", available at Amazon and other fine booksellers. (I believe a peek at most of chapter 2 is available in the "look inside" feature on Amazon.) – spencer7593 Mar 22 '18 at 22:40

Condensing Multiple Rows to a Single Summary Row

1 Answers1