0

I have a MySQL table where all the company sales for a given period are grouped by unit, date and, inside an array, all the sales representatives IDs that were involved in each sale. The first sales_rep ID in each array shows which sales representative was responsible for closing each sale. I am trying to elaborate a query that returns the unit, the date and the sales_rep ID for each sales representative that closed each sale. It is ok if a sales_rep ID is shown more than once for given date.

What I have as input: 

unit_id   date   unit_sales   sales_reps
427     2019-07-01   3     [["19823508","19823510",""],["23661230","23661227","23411261",""],["23411257","19823508",""]]
466     2019-07-26   2     [["23222763","23222766","26726848","26726849",""],["23222763",""]]

The desired output is: 

unit_id   date      sales_rep
427     2019-07-01  19823508
427     2019-07-01  23661230
427     2019-07-01  23411257
466     2019-07-26  23222763
466     2019-07-26  23222763
GMB
  • 216,147
  • 25
  • 84
  • 135
Luke Briplin
  • 1
  • 1

1 Answers1

2

This is too long for a comment.

You should really consider fixing your data model. Storing structured data (csv, arrays, etc) in a text column will bite you in many ways. Typically, what you want to do here will necessarily involve a SQL with lot of complexity. This can be avoided by adopting a data model that better fits to your use case, for example:

  • create a separate table to store the list of sales reps that were involved in each unit sale, with a boolean column that indicates which sales rep closed the sale; this seems like the right way to do it

  • or, as an intermediate solution, transform your text column to a json column: this is less clean, but at least you could take advantage of json functions when accessing your data

Related reading: Is storing a delimited list in a database column really that bad?

GMB
  • 216,147
  • 25
  • 84
  • 135