Returning Multiple Values of the same id in array

Question

I'm pulling information from 3 different tables in MSSQL 2008 and I'd like to get the SUM of CC_qty as well as each Location condensed into one field per id. If this can be done in the query itself that would be fantastic - listagg and GROUP_CONCAT are not cutting it. Otherwise I've been working with array_reduce, array_merge, array_diff to no avail.

Here is my query and the original array:

SELECT a.id, a.qty, b.locationID, b.CC_qty, c.Location FROM (
  SELECT left(id, 10) as id, MAX(qty) as qty
  FROM db1
  WHERE id like 'abc-abc%'
  GROUP BY left(id, 10)
) as a
JOIN (
  SELECT locationID, left(SKU, 10) as SKU, CC_qty FROM db2
  WHERE CC_qty > 25
) as b on a.abc-abc = b.SKU
JOIN (
  SELECT locationID, Location FROM db3
) as c on b.locationID = c.locationID


Array
(
    [0] => Array
        (
            [id] => abc-abc-12
            [qty] => 0
            [locationID] => 276
            [CC_qty] => 250
            [Location] => NOP11
        )

    [1] => Array
        (
            [id] => abc-abc-12
            [qty] => 0
            [locationID] => 310
            [CC_qty] => 1385
            [Location] => NOP01
        )

    [2] => Array
        (
            [id] => abc-abc-23
            [qty] => 0
            [locationID] => 84
            [CC_qty] => 116
            [Location] => NOP06
        )

    [3] => Array
        (
            [id] => abc-abc-23
            [qty] => 0
            [locationID] => 254
            [CC_qty] => 432
            [Location] => NOP08
        )

    [4] => Array
        (
            [id] => abc-abc-23
            [qty] => 0
            [locationID] => 228
            [CC_qty] => 101
            [Location] => NOP04
        )

    [5] => Array
        (
            [id] => abc-abc-34
            [qty] => 0
            [locationID] => 254
            [CC_qty] => 436
            [Location] => NOP08
        )

    [6] => Array
        (
            [id] => abc-abc-34
            [qty] => 0
            [locationID] => 254
            [CC_qty] => 62
            [Location] => NOP08
        )

    [7] => Array
        (
            [id] => abc-abc-45
            [qty] => 0
            [locationID] => 75
            [CC_qty] => 89
            [Location] => NOP05
        )

    [8] => Array
        (
            [id] => abc-abc-45
            [qty] => 0
            [locationID] => 202
            [CC_qty] => 372
            [Location] => NOP07
        )

)

This is my desired output, for simplicity of knowing what information I absolutely require I've removed qty and locationID but those don't have to be removed:

 Array
    (
        [0] => Array
            (
                [id] => abc-abc-12
                [CC_qty] => 1635
                [Location] => NOP11, NOP01
            )

        [1] => Array
            (
                [id] => abc-abc-23
                [CC_qty] => 649
                [Location] => NOP06, NOP08, NOP04
            )

        [2] => Array
            (
                [id] => abc-abc-34
                [CC_qty] => 495
                [Location] => NOP08

        [3] => Array
            (
                [id] => abc-abc-45
                [CC_qty] => 461
                [Location] => NOP05, NOP07
            )

    )

Thanks for looking!

Answer 1

I don't have any experience with MSSQL, but I feel rather confident that it provides the necessary functionality to merge, sum, and concatenate. Anyhow, I am compelled to post an answer because I find the answer from Thomas to be unrefined.

Essentially, you should use the id values as temporary keys to determine if you are processing the first occurrence of the group or a subsequent occurrence. On the first encounter, just save the whole row to the output array. For all future rows belonging to the same group, just sum and concatenate the desired values.

To remove the temporary keys in the result array, just call array_values($result).

Code: (Demo)

$array = [
    ['id' => 'abc-abc-12', 'qty' => 0, 'locationID' => 276, 'CC_qty' => 250, 'Location' => 'NOP11'],
    ['id' => 'abc-abc-12', 'qty' => 0, 'locationID' => 310, 'CC_qty' => 1385, 'Location' => 'NOP01'],
    ['id' => 'abc-abc-23', 'qty' => 0, 'locationID' => 84, 'CC_qty' => 116, 'Location' => 'NOP06'],
    ['id' => 'abc-abc-23', 'qty' => 0, 'locationID' => 254, 'CC_qty' => 432, 'Location' => 'NOP08'],
    ['id' => 'abc-abc-23', 'qty' => 0, 'locationID' => 228, 'CC_qty' => 101, 'Location' => 'NOP04'],
    ['id' => 'abc-abc-34', 'qty' => 0, 'locationID' => 254, 'CC_qty' => 436, 'Location' => 'NOP08'],
    ['id' => 'abc-abc-34', 'qty' => 0, 'locationID' => 254, 'CC_qty' => 62, 'Location' => 'NOP08'],
    ['id' => 'abc-abc-45', 'qty' => 0, 'locationID' => 75, 'CC_qty' => 89, 'Location' => 'NOP05'],
    ['id' => 'abc-abc-45', 'qty' => 0, 'locationID' => 202, 'CC_qty' => 372, 'Location' => 'NOP07'],
];

$result = [];
foreach ($array as $row) {
    if (!isset($result[$row['id']])) {
        $result[$row['id']] = $row;
    } else {
        $result[$row['id']]['qty'] += $row['qty'];                      // SUM
        $result[$row['id']]['locationID'] .= ", " . $row['locationID']; // CONCAT
        $result[$row['id']]['CC_qty'] += $row['CC_qty'];                // SUM
        $result[$row['id']]['Location'] .= ", " . $row['Location'];     // CONCAT
    }
}
var_export(array_values($result));

Output:

array (
  0 => 
  array (
    'id' => 'abc-abc-12',
    'qty' => 0,
    'locationID' => '276, 310',
    'CC_qty' => 1635,
    'Location' => 'NOP11, NOP01',
  ),
  1 => 
  array (
    'id' => 'abc-abc-23',
    'qty' => 0,
    'locationID' => '84, 254, 228',
    'CC_qty' => 649,
    'Location' => 'NOP06, NOP08, NOP04',
  ),
  2 => 
  array (
    'id' => 'abc-abc-34',
    'qty' => 0,
    'locationID' => '254, 254',
    'CC_qty' => 498,
    'Location' => 'NOP08, NOP08',
  ),
  3 => 
  array (
    'id' => 'abc-abc-45',
    'qty' => 0,
    'locationID' => '75, 202',
    'CC_qty' => 461,
    'Location' => 'NOP05, NOP07',
  ),
)

Answer 2

Being that I left an answer for MySQL, it wasn't going to work for this. I don't know MSSQL well enough to use it, so here's a way to do it with PHP so I don't leave you completely without an answer.

$arr = array
(
    array
    (
        'id' => 'abc-abc-12',
        'qty' => 0,
        'locationID' => 276,
        'CC_qty' => 250,
        'Location' => 'NOP11'
    ),
    array
    (
        'id' => 'abc-abc-12',
        'qty' => 0,
        'locationID' => 310,
        'CC_qty' => 1385,
        'Location' => 'NOP01'
    ),
    array
    (
        'id' => 'abc-abc-23',
        'qty' => 0,
        'locationID' => 84,
        'CC_qty' => 116,
        'Location' =>  'NOP06'
    )
);

$combinedArr = array();

foreach ($arr as $a)
{
    $found = false;

    foreach ($combinedArr as $i => $b)
    {
        if ($b['id'] == $a['id'])
        {
            $found = true;
            $locs  = explode(',', $a['Location']);

            $combinedArr[$i]['CC_qty'] += $a['CC_qty'];

            if (!in_array($b['Location'], $locs))
            {
                $locs[] = $b['Location'];
                $combinedArr[$i]['Location'] = implode(', ', $locs);
            }
        }
    }

    if (!$found)
        $combinedArr[] = $a;
}

print_r($combinedArr);

/*
Array
(
    [0] => Array
        (
            [id] => abc-abc-12
            [qty] => 0
            [locationID] => 276
            [CC_qty] => 1635
            [Location] => NOP01, NOP11
        )

    [1] => Array
        (
            [id] => abc-abc-23
            [qty] => 0
            [locationID] => 84
            [CC_qty] => 116
            [Location] => NOP06
        )

)
*/