Clever Geek Handbook

Creating a multidimensional array from a table where duplicate IDs have the same parent key

I have a table like this:

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

I need 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 request:

SELECT * FROM table ORDER BY office id

Is there a better query for this case?
Is there a simple and small method to create an array like above?

EDIT: I'm using SQL Server for this ... so sadly there is no GROUP_CONCAT etc.):

Thank!

+3


source to share


2 answers


Better to use LISTAGG . i.e.

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


once you get the above sql result, we can designate each line with a ',' separator and put the tokenized data into an array.

+3


source


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 results like:



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

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

+2


source






More articles:


All Articles