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:

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!

Thomas · Accepted Answer · 2018-05-17T14:40:57.283

1

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
        )

)
*/

edited May 17 '18 at 14:40

answered May 17 '18 at 13:56

Thomas

442
4
11

I want to make sure I understand the format of the 'group by' you suggested: ```SELECT a.id, a.qty, b.locationID, sum(b.CC_qty) as CC_qty, group_concat(distinct c.Location separator ', ') as 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 group by `a`.`id` ``` ? – mezzomix May 17 '18 at 14:14
The `group by` is to make all of the rows with the same `a.id` be grouped together. Then, when we do a `sum()` and `group_concat()`, it only pulls together rows in that group. – Thomas May 17 '18 at 14:16
Looks right. I should mention that the backticks are optional around column, table, and alias names if that's what's throwing you off. It's just a habit for me to use them. – Thomas May 17 '18 at 14:17
Thank you for verifying @Thomas, the backticks didn't throw me off it's the error I'm getting: ```Msg 156, Level 15, State 1. Incorrect syntax near the keyword 'as'. Msg 102, Level 15, State 1. Incorrect syntax near 'separator'. ``` I'm googling now – mezzomix May 17 '18 at 14:20
1

Sorry. I didn't realize you were using MSSQL. Maybe this will help you https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 Since you mentioned having tried it, I assumed you could use `group_concat()`. Maybe try removing the separator and it should by default just separate by a comma if the function does exist. – Thomas May 17 '18 at 14:24
That's alright :) I'm hoping to migrate in 6 months which will eliminate a lot of questions. Unfortunately `group_concat` is not a recognized built-in function so I can't use it. I'll follow through the link you included and see what I can do. Thanks! – mezzomix May 17 '18 at 14:27
1

I had to at least give a valid answer, so I updated my post. Hope it helps. – Thomas May 17 '18 at 14:41
I'll give is a shot and report my findings. Thank you so much! – mezzomix May 17 '18 at 14:43
Thank you very much @Thomas! We didn't get it working in MSSQL but your PHP answer gave the desired output. Thank you! – mezzomix May 17 '18 at 15:04

score 0 · Answer 2 · answered Jun 18 '20 at 14:31

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',
  ),
)

Returning Multiple Values of the same id in array

2 Answers2