0

What's the best way to combine rows with similar values into a single row while still keeping a row data. I need to get quantities from a database with different window_id but with the same meal_id. I would like them to be in the same object/same row so that I can map through them in a table. What's the best way to do this? Thank you

Starting Data

--------------------------------------------
window_id  |  meal_id  |  meal_name  |  qty
--------------------------------------------
1           1           Salad         4
2           1           Salad         6
3           1           Salad         7
1           2           Pork          4
2           2           Pork          9
3           2           Pork          10

**OR** in Javascript Object
{
 windowId: 1
 mealId: 1,
 mealName: "Salad",
 qty: 4
},
{
 windowId: 2
 mealId: 1,
 mealName: "Salad",
 qty: 6
},
{
 windowId: 3
 mealId: 1,
 mealName: "Salad",
 qty: 7
}

Desired Result

--------------------------------------------------------------
meal_id  |  meal_name  |  qty1  |  qty2  |  qty3...
--------------------------------------------------------------
1           Salad         4        6        7
2           Pork          4        9        10



**OR** in Javascript Object

{
 mealId: 1,
 mealName: "Salad",
 qty1: 4,
 qty2: 6,
 qty3: 7
}
dngbll
  • 41
  • 1
  • 6
  • Is your data coming from SQL? If it is, then this sort of data transformation would better be done in the query. That said, you may have a problem, in that you appear (possibly?) to have n-number of qty cells to consolidate... Will it be OK to have them all running horizontally? Or do you know that there are few enough that it will work? – HumanJHawkins Feb 02 '20 at 05:04
  • Yes, it is. There's actually n-number of qty cells. because there will be new instances of the meal for every meal window added. – dngbll Feb 02 '20 at 05:23
  • You might want to post the SQL of your query to https://dba.stackexchange.com/. It would certainly be possible to write a javascript function to do what you need, but I think it may be easier to get the data straight from the database as you need it. Or, see the solutions to this very similar need: https://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server – HumanJHawkins Feb 02 '20 at 05:40
  • @HumanJHawkins, I don't think sql supports having a dynamic number of columns (qty1, qty2, qty3...) so this should be done in JS with Array.reduce. Starting from the array of JS objects in starting data, you're gonna call array reduce on that and depending on whether the next element's mealId exist in the result, you're either dynamically adding a new indexed qty property (qty1, qty2, ...) to that object or adding a new element to the array. – JoeRaid Feb 02 '20 at 07:12
  • @JoeRaid, You can get the desired result with SQL using nested queries, but your idea might be better. In any case, I am hung up on the idea that this sounds like a problematic solution even if successful. I would consider switching to a single "qty" field with a comma-delimited concatenation of all the qty values. But even that would get unwieldy once more than about 20 "qty"s were in it. So, perhaps concatenate the most recent x-number of qty fields into a single comma-delimited field. But make it a hyperlink that will bring up the complete data on request. (Just a thought.) – HumanJHawkins Feb 04 '20 at 01:52

1 Answers1

0

standard javascript you will have to write your own flattening function.

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/flat

CaveTroll
  • 21
  • 3