返回数组中相同id的多个值
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!
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
)
)
*/