convert hierarchy structure to parent children pair please... 1

[peac3]

Hello SQL legends,

I have a table with a family structure vertical as below

ROW_N FamilyID CompanyName HierarchyLevel ClientID
1      A        XX1          0              A1
2      A        XX2          1              A12
3      A        XX3          2              A345
4      A        XX4          2              A456
5      A        XX5          3              A6788
6      A        XX6          2              A346
7      A        XX7          3              A6789
8      B        XX8          0              B1
9      B        XX9          1              B11 
10     B        X10          2              B345
11     B        X11          2              B567 
12     B        X12          3              B6789

We want to build parent-children relationships from the above structure to horizontal using ClientID as keys as per below.
The logic is the parent is the closest one level higher hierarchy order by ROW_N.

Hence expected output as per below

ROW_N FamilyID ParentID ParentName ChildrenID ChildrenName HierarchyLevel 
1      A        A1         XX1        A12        XX2          1
2      A        A12        XX2        A345       XX3          2
3      A        A12        XX2        A456       XX4          2
4      A        A456       XX4        A6788      XX5          3
5      A        A12        XX2        A346       XX6          2
6      A        A346       XX6       A6789       XX7          3
7      B        B1         XX8        B11        XX9          1
8      B        B11        XX9        B345       X10          2
9      B        B11        XX9        B567       X11          2
10     B        B567        X11       B6789      X12          3

 

[mikrom]

I tried this:

select 
  t1.FAMILYID,
  t1.CLIENTID,
  t1.COMPANYNAME,
  t2.CLIENTID,
  t2.COMPANYNAME,
  t2.HIERARCHYLEVEL
from 
  PEAC3 t1
  inner join 
  PEAC3 t2 on t1.FAMILYID = t2.FAMILYID and 
              t2.HIERARCHYLEVEL = t1.HIERARCHYLEVEL + 1
;

Output

 FAMILYID  CLIENTID    COMPANYNAME  CLIENTID    COMPANYNAME  HIERARCHYLEVEL
    A      A1              XX1      A12             XX2             1      
    A      A12             XX2      A345            XX3             2      
    A      A12             XX2      A456            XX4             2      
    A      A345            XX3      A6788           XX5             3      
    A      A456            XX4      A6788           XX5             3      
    A      A346            XX6      A6788           XX5             3      
    A      A12             XX2      A346            XX6             2      
    A      A345            XX3      A6789           XX7             3      
    A      A456            XX4      A6789           XX7             3      
    A      A346            XX6      A6789           XX7             3      
    B      B1              XX8      B11             XX9             1      
    B      B11             XX9      B345            X10             2      
    B      B11             XX9      B567            X11             2      
    B      B345            X10      B6789           X12             3      
    B      B567            X11      B6789           X12             3

 

[TamarGranor]

You're on the right track, but what you need is a recursive CTE to drill all the way down:

[pre]
WITH Hier (FamilyID, ClientID, CompanyID, ChildClientID, ChildCompanyID, HierarchyLevel) AS

(SELECT FamilyID, ParentID AS ClientID, CompanyID,
CAST('' AS NVARCHAR(5) AS ChildClientID,
CAST('' AS NVARCHAR(3) AS ChildCompanyID,
HierarchyLevel
FROM PEAC3
WHERE HierarchyLevel = 0
UNION ALL
SELECT T1.FamilyID, T1.ParentID AS ClientID, T1.CompanyID,
T2.ClientID, T2.CompanyID, T2.HierarchyLevel
FROM PEAC3 T1
JOIN PEAC3 T2
ON t1.FAMILYID = t2.FAMILYID and
t2.HIERARCHYLEVEL = t1.HIERARCHYLEVEL + 1)

SELECT *
FROM Hier
WHERE HierarchyLevel > 0;
[/pre]

Untested, but should give you the basic idea.

Tamar

 

[peac3]

Thanks Mikrom,

The issue here is the children cannot have multiple parents.
Hence it is on the right track but the output is not quite entirely correct,

Here I crossed the unexpected records

FAMILYID  CLIENTID    COMPANYNAME  CLIENTID    COMPANYNAME  HIERARCHYLEVEL
    A      A1              XX1      A12             XX2             1      
    A      A12             XX2      A345            XX3             2      
    A      A12             XX2      A456            XX4             2      
    [s]A      A345            XX3      A6788           XX5             3[/s]      
    A      A456            XX4      A6788           XX5             3      
   [s] A      A346            XX6      A6788           XX5             3[/s]      
    A      A12             XX2      A346            XX6             2      
    [s]A      A345            XX3      A6789           XX7             3[/s]      
    [s]A      A456            XX4      A6789           XX7             3[/s]      
    A      A346            XX6      A6789           XX7             3      
    B      B1              XX8      B11             XX9             1      
    B      B11             XX9      B345            X10             2      
    B      B11             XX9      B567            X11             2      
    [s]B      B345            X10      B6789           X12             3[/s]      
    B      B567            X11      B6789           X12             3

 

[mikrom]

If you have B6789 on Level=3, how do you know whether B345 or B567 on Level=2 is its parent ?

ROW_N FamilyID CompanyName HierarchyLevel ClientID
...
...
10     B        X10          2              B345
11     B        X11          2              B567 
12     B        X12          3              B6789

 

[Andrzejek]

Instead of converting one convoluted table setup to another even more complicated arrangement of data, wouldn't be easier to set up two tables (ParentTBL and ChildTBL) and have truly Parent-Child relations between the two (Primary Key to Foreign Key)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[peac3]

Hi mikrom,

Good question, the parent is the one above the children, sorted by row_n.
so B345 and A345 have no children in this case.

Think about it like hierarchy.

 

[TamarGranor]

Good question, the parent is the one above the children, sorted by row_n.
so B345 and A345 have no children in this case.

So, this is the problem. If you want to use a query, you need to have data in the table that lets you connect the right records. Position is meaningless in SQL.

You should add a column in each record that holds the key for the parent record. Then, a modified version of the code I gave you yesterday will get what you need. (You'll want to match the child ID to the parent ID, rather than matching on family id and hierarchy level.)

Tamar

 

[peac3]

So, this is the problem. If you want to use a query, you need to have data in the table that lets you connect the right records. Position is meaningless in SQL.

You should add a column in each record that holds the key for the parent record. Then, a modified version of the code I gave you yesterday will get what you need. (You'll want to match the child ID to the parent ID, rather than matching on family id and hierarchy level.)

Tamar

That is the raw data given to us Tamar, if we already had children and a parent table, that should be straight forward and I wouldn't have posted the question here.
This is a complex matter hence I need direction from you guys as an expert. Can you show me something more tangible on how to separate children and parent from above raw data?

 

[Chris Miller]

It would help to know what this data means, in the first place. What's natural to you isn't known to anyone until you'd explain.

I can make a few guesses and surely you don't need to tell me what a company name is, but hierarchy can be interpreted in mayn ways. Hierarchy level on a treeview, for example. Is it that? Hierarchy is a term that has many meanings.

Now, please, don't just tell what hierarchy mean, could you please tell more about what this data is for at all? That would be extremely helpful to know.

Chriss

 

[Chris Miller]

If we talk about treeview data here, you can't turn a tree of items into a single list where each item has exactly one parent and one child, you'd need to make arbitrary decisions for what's the next child of a leaf node item, for example. And you'd make siblings parent/childs of each other.

You're facing either an impossible to solve problem or you make some arbitrary decisions that turn this into something not relating to the original tree relationships. How is that helpful at all?

If this data represents treeview data and hierarchy is just telling on which level of a treeview a node is, then in usual database structures of treeview data, hierarchy is not stored at all, it is a result of where a node sorts into the tree. A root node will have no parent and be on hierarchy level 1, the hierarchy of a node in general would just count the distance to the root node, but you would not store this in data. So that's already strange conceptually and maybe points out this isn't about a tree of nodes. I would even wonder what nodes would represent, companies or their clients. If either of these, what does the other data relate to? It's all unclear to me.

Chriss

 

[peac3]

If we talk about treeview data here, you can't turn a tree of items into a single list where each item has exactly one parent and one child, you'd need to make arbitrary decisions for what's the next child of a leaf node item, for example. And you'd make siblings parent/childs of each other.

You're facing either an impossible to solve problem or you make some arbitrary decisions that turn this into something not relating to the original tree relationships. How is that helpful at all?

If this data represents treeview data and hierarchy is just telling on which level of a treeview a node is, then in usual database structures of treeview data, hierarchy is not stored at all, it is a result of where a node sorts into the tree. A root node will have no parent and be on hierarchy level 1, the hierarchy of a node in general would just count the distance to the root node, but you would not store this in data. So that's already strange conceptually and maybe points out this isn't about a tree of nodes. I would even wonder what nodes would represent, companies or their clients. If either of these, what does the other data relate to? It's all unclear to me.

Hi Chris,

The source data is a tree view hierarchy in which information is provided by our supplier with a hierarchy level. We just need to convert the raw data into a parent-child relationship so we can compare our source data whether they match.
I think Microm almost solved it, it just needs to have a little more crack.

 

[Chris Miller]

Well, that confirms what I saw in it, it still doesn't explain details. I told you a treeview structure in gneeral can't be converted into a single double linked list, what records with each parent and child ids are. So what you aks for shows you don't understand the difference of a treeview and list data structure. Even if you would argue now, that you don't need one list but multiple lists could be created, they will never reflect the relationships of data that's encoded in the original treeview structure.

So what you did with your expected result usually is a finde specification to verify a result, but when you only give that and don't explain the rules to get to that result, you don't allow anyone to find a solution to do all that with sql or more generally any code.

That said I'm out and my bet is on you not getting any better results than were alread proposed. You didn't look into Tamars solution, or didn't react to it, at least. So sadly, this thread will also fade out with loose ends.

PS: It may not matter to you that the final result you want isn't reflecting all the treeview relationships. One possible and very valid task and concern could be how to traverse all nodes of a treeview in some order, which could be depth first or breadth first. That results in a linked list, if you like, also a doubly linked list, but even a list at all with a sequence number would suffice for that, so it's unlikely why you want a doubly linked list. If it's that, depite the strange form of a result you want, you could say so, but just providing an expected result doesn't define what you want in general.

Chriss

 

[Chris Miller]

You have a core question, peac:

...how to separate children and parent..

...We just need to convert the raw data into a parent-child relationship...

Well, I can't be sure until you confirm, but I would say any family A data has no relationship to family B data, so the raw data in your initial post already llists two treeviews.
Data is in a parent chjild relationship when thzeir hiarchy level differes by 1 the lower hierarchy is the parent of the higher hierarchy.

So what's easy enough to do is to find pairs of nodes, and I'd only ever let children point to their parent, not parents point to one of their children, that's not what you ever do in database design.

A1 and B1 are roots node by that interpretation, all other nodes have parents. But, for example, as mikrom once pointed out by a question, you can't really know whether B345 or B567 is parent of B6789. You saidthat should be decided by row number, but that's an arbitrary choice, not necvessarily the truth.

So all in all, the raw data isn't completely defining the exact parent-child relationships, you could deduce multiple ways nodes are related. You can't purify and enhance bad raw data, so you could only make assumptions and arbitrary choices about this specific taks to find parent/child relationships. the hierarchy level can just help to categorize ...,grandparents, parents, children, grandchildren, ..., but don't point out direct relationships unless you only have one record for one hierarchy level.


Chriss

 

[Chris Miller]

What also remains unspecified:

I would even wonder what nodes would represent, companies or their clients. If either of these, what does the other data relate to?

Are clientIDs the unique identifiers of company names? Or are clientIDs the identifier of clients of that company. Is this data reflecting companys as your companies clients or is it relationships between companies in general, independent from your company, or not in the point of view of your company.

All that may be clear to you, but is untold.

Chriss

 

[peac3]

Are clientIDs the unique identifiers of company names?

Yes, it is unique of this table as per example above.

 

[Chris Miller]

That's only half an answer and the less important part of it. I always assume ay ID to be unique, that's normal.

Is clientID just another way to specify a company? Or is the company having clients, which would mean clientID is specifying another customer or even a single person that is a client of that company. That would give two totally different interpretatoins of the same data.

Chriss

 

[mikrom]

If the SQL query becomes too complicated, IMHO it is better to write a program in conventional programming language. Because a source written in a programming language is better maintainable than complicated SQL statements.

 

[Chris Miller]

I second that, too, no matter whether you know or don't know exact meanings of data, it's already clear something like traversing treeviews fully compares to problems like the travelling salesman ideal route and those are also easier solved with general code, not SQL queries. It doesn't even matter whether SQL is considered Turing complete or the problem here is simpler than the traveling salesman problem.

Chriss

 

[peac3]

If the SQL query becomes too complicated, IMHO it is better to write a program in conventional programming language. Because a source written in a programming language is better maintainable than complicated SQL statements.

I can write c#/python, would you tell me how the best logic to solve the problem?