从具有相同父键的重复id的表创建多维数组

I have a table like this:

我有一张这样的桌子:

--primary id---office id----
|----1-------|---10--------|
|----2-------|---10--------|
|----3-------|---20--------|
|----4-------|---10--------|
|----5-------|---20--------|
----------------------------

I want an object/array like this:

我想要一个像这样的对象/数组:

Array = array(
  "10" => array(
      "Primary ID" => 1,
      "Primary ID" => 2,
      "Primary ID" => 4
   ),
  "20" => array(
      "Primary ID" => 3,
      "Primary ID" => 5
   )
)

My query would be:

我查询的方法是:

SELECT * FROM table ORDER BY office id

Is there a better query for this case?
Is there a simple and small method for creating an Array like that above?

有更好的查询吗?是否有一个简单和小的方法来创建这样的数组?

EDIT: I am using SQL Server for this... so sadly no GROUP_CONCAT etc... ):

编辑:我正在使用SQL Server…所以很遗憾没有GROUP_CONCAT等…

Thanks!

谢谢!

2 个解决方案

#1

Its better to use LISTAGG function . i.e.

最好使用LISTAGG函数。即。

SELECT office id, LISTAGG(primary id, ', ')
FROM table
GROUP BY office id
ORDER BY office id

once you get the result of above sql, we can tokenise each row by delimiter ',' and put the tokenised data in array.

一旦得到上面sql的结果，我们就可以按分隔符'对每一行进行标记，并将标记数据放入数组中。

#2

You can use the following query:

您可以使用以下查询:

SELECT primary id, GROUP_CONCAT(office id) as office_id
FROM table
GROUP BY primary_id;

This will return the results as:

这将返回结果如下:

primary_id   office_id
---------------------------
10           1,2,4
20           3,5

This would make it easier for you to process it and store it in array.

这将使您更容易处理它并将其存储在数组中。

#1