Hierarchical query with non unique id's

Question

I'm trying to create a query to transform a hierarchical table structure to columns.

Each Node has a parent. But the Node is not a unique number. In each root-leaf it is unique. But can exist under an other parent.

I tried with a CTE. And with multiple SELECT .. UNION ...

But I'm unable to filter out that it is in the same root-leaf. For example, I'm getting the two selected lines for both parent 01 as 09.

The max depth is 4.

And the result I want is. RUBRICCODE;NAME0;NAME1;NAME2;NAME3;NAME4 0100000000;Financieel Administratieve Documenten;;;; 0100100000;Financieel Administratieve Documenten;Patientenadministratie;;; ...

select * from (

  select t0.RUBRICCODE, t0.PATH, t0.NAME Rubr0, '' Rubr1, '' Rubr2, '' Rubr3, '' Rubr4
  from dacs_treestructure t0
  where t0.DEPTH = 0

  UNION ALL

  select t1.RUBRICCODE, t1.PATH, t0.NAME Rubr0, t1.NAME Rubr1, '' Rubr2, '' Rubr3, '' Rubr4
  from dacs_treestructure t0
  join dacs_treestructure t1 on t0.NODE = t1.PARENT
  where t0.DEPTH = 0 and t1.DEPTH = 1

  UNION ALL

  select t2.RUBRICCODE, t2.PATH, t0.NAME Rubr0, t1.NAME Rubr1, t2.NAME Rubr2, '' Rubr3, '' Rubr4
  from dacs_treestructure t0
  join dacs_treestructure t1 on t0.NODE = t1.PARENT
  join dacs_treestructure t2 on t1.NODE = t2.PARENT
  where t0.DEPTH = 0 and t1.DEPTH = 1 and t2.DEPTH = 2

) x order by 1

Result of the query. The selected lines are from parent 09. And should not exist.

Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it, your current attempt and your desired results. For more details, [read this.](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) — Zohar Peled, Nov 08 '18 at 21:32
Please don't post the solution as a part of the question. post it as an answer to your own question and accept it after the time limit is over. — Zohar Peled, Nov 09 '18 at 10:18
I'm sorry, I looked over the button. Changed it, and added as separate. — Stinus, Nov 09 '18 at 10:23

score 1 · Accepted Answer · answered Nov 09 '18 at 10:22

I managed to solve my problem using the already existing column PATH. With replacing the forward slash to an XML structure, I managed to get the different parts as columns. And then I joined per level again to the source table to fetch the description for that level.

WITH Split_Tree (RUBRICCODE,XmlPath)
AS
(
    SELECT
         RUBRICCODE
        ,CONVERT(XML,'<Paths><Path>' + REPLACE(PATH,'/', '</Path><Path>') + '</Path></Paths>') AS XmlPath
     FROM dacs_treestructure
)
SELECT t.RUBRICCODE, t0.NAME NAME0, t1.NAME NAME1, t2.NAME NAME2, t3.NAME NAME3, t4.NAME NAME4
FROM (
SELECT
     RUBRICCODE
    ,XmlPath.value('/Paths[1]/Path[1]','varchar(15)') AS PATH0
    ,XmlPath.value('/Paths[1]/Path[2]','varchar(15)') AS PATH1
    ,XmlPath.value('/Paths[1]/Path[3]','varchar(15)') AS PATH2
    ,XmlPath.value('/Paths[1]/Path[4]','varchar(15)') AS PATH3
    ,XmlPath.value('/Paths[1]/Path[5]','varchar(15)') AS PATH4
FROM Split_Tree) t
LEFT JOIN dacs_treestructure t0 on t0.DEPTH = 0 AND t0.PATH = (PATH0)
LEFT JOIN dacs_treestructure t1 on t1.DEPTH = 1 AND t1.PATH = (PATH0 + '/' + PATH1)
LEFT JOIN dacs_treestructure t2 on t2.DEPTH = 2 AND t2.PATH = (PATH0 + '/' + PATH1 + '/' + PATH2)
LEFT JOIN dacs_treestructure t3 on t3.DEPTH = 3 AND t3.PATH = (PATH0 + '/' + PATH1 + '/' + PATH2 + '/' + PATH3)
LEFT JOIN dacs_treestructure t4 on t4.DEPTH = 4 AND t4.PATH = (PATH0 + '/' + PATH1 + '/' + PATH2 + '/' + PATH3 + '/' + PATH4)
GO

Hierarchical query with non unique id's

1 Answers1