0

I have a storage application that stores data on change, about 200 columns with a time stamp and ID. What I am trying to do is format each row into a table layout. Raw data: Example of raw data

Here is how I would like to return the data: Example of formatted data

I am new to MySQL so I apologize if this is an obvious answer, any help is appreciated!

Barmar
  • 741,623
  • 53
  • 500
  • 612
Mitch Landreth
  • 1
  • You should normalize your table structure. Having hundreds of `ingredient_#_xxx` columns is the wrong way to do it. – Barmar Apr 06 '17 at 20:11
  • 1
    You should just have 4 columns: `time_stamp`, `ingredient_name`, `ingredient_actual`, `ingredient_target`. Then you can use `ORDER BY time_stamp` to get all the ingredients with the same timestamp together. – Barmar Apr 06 '17 at 20:15
  • @Barmar in principle I agree, but sometimes you have to work with what you have got. Anyway, unpivoting in mysql has already been answered here on SO, see duplicate topic. – Shadow Apr 06 '17 at 20:51
  • @Shadow These aren't even dynamic columns, he can just use a `for($i = 1; $i < $max_ingredients; $i++)` and get each of the ingredients. – Barmar Apr 06 '17 at 20:55
  • @Barmar Yeah, he could do that - provided he uses php. The question is tagged as mysql and sql, henc3 the duplicate in sql only. – Shadow Apr 06 '17 at 20:58
  • Yeah I agree the table structure should be normalized, unfortunately, the initial setup was done by someone else and there is years of data which would be to costly to remake or start over. – Mitch Landreth Apr 07 '17 at 21:45

0 Answers0