Unanswered: Creating a hierarchy of parent organizations
Slightly complicated issue I've tried solving but haven't got anywhere.
I have hundreds of rows in Table A, each with an Organization ID, Organization Name, and Parent ID.
There's also an Organization table which contains the Organization mappings (this also has an Organization ID, Organization Name, Parent ID). - The parent ID is basically an Organization ID, referring to the Parent Organization (and this is recursive, so each Parent can have another Parent etc.)
The problem I have is in Table A. I need to create new columns showing the parent hierarchy starting from the Organization Name in the table.
This step is quite easy, simply using a JOIN with the Organization table ON Parent ID works pretty well.
The complication comes here:
-Every Organization in Table A is the lowest level in its hierarchy (i.e. it doesn't have any children).
- Every Organization in Table A has a varying amount of parents (i.e. some might have 1 parent who then has another parent, or some might have 1 parent as the BOSS (top most level))
I need to organise the Table so that each organization can have its parents listed beside (one in each column), and should show NULL if it doesn't have a parent at a certain level. - This bit is difficult to explain, so I've attached an image
So essentially, regardless of how many parents an organization has, the top most parent (BOSS) should always appear under the same column.(image explains this)
I'd really appreciate some help, if you need any clarifications let me know!
That example matches my explanation about the normal way of creating this hierarchy (Quoted from my thread: "The problem I have is in Table A. I need to create new columns showing the parent hierarchy starting from the Organization Name in the table.
This step is quite easy, simply using a JOIN with the Organization table ON Parent ID works pretty well.")
Referring to that example, what I need is to have the highest level in the same column. (i.e. the root 'Animal' should ONLY appear in up3_name. If the leaf doesnt have any other parents, then up2_name and up1_name should just be NULL)
Basically the idea is to be able to GROUP BY a certain organization (but obviously this cant be done easily if the organization you want to group by is mentioned in multiple columns)