I am having a large set of data in 2 columns where I need to create hierarchical structure from it. Below is the input table structure.
| Parent | Child |
|---|---|
| P4S/JJ | P4S/JJ/00/AMS |
| P4S/JJ | P4S/JJ/SIM |
| P4S/JJ/00 | P4S/JJ/00/ALMS |
| P4S/JJ/00 | P4S/JJ/00/CNT VQ/FW-LL-01 |
| P4S/JJ/00 | P4S/JJ/00/CNT VQ/FW-LL-02 |
| P4S/JJ/00 | P4S/JJ/00/QQ |
| P4S/JJ/00 | P4S/JJ/00/QQMQ |
| P4S/JJ/00 | P4S/JJ/00/FGS |
| P4S/JJ/00 | P4S/JJ/00/IAMS |
| P4S/JJ/00 | P4S/JJ/00/ICS-SA |
Requirement is to find all the Child of Parent. But the issue here is Parent can also be a child of another Parent in the same column.
I was trying with below query and nowhere near to creating a hierarchical structure. Query tried:
with cteP as (
select Parent,Child
from [dbo].[Data]
where Parent is not null
Union All
Select r.Parent,r.Child
From [dbo].[Data] r
inner join cteP on r.Parent = cteP.Parent
Select *
From cteP A
--Order By A.Parent
option (maxrecursion 0)
I tried using hierarchy id and getting the error "Implicit conversion from data type hierarchyid to varchar is not allowed. Use the CONVERT function to run this query."
I want my output table in below format until the child is null:
| Parent | Child | Sub Child1 |
|---|---|---|
| P4S/JJ | P4S/JJ/00/AMS | |
| P4S/JJ | P4S/JJ/SIM | P4S/JJ/SIM/MMFOD01 |
| P4S/JJ | P4S/JJ/SIM | P4S/JJ/SIM/MMSCP01 |
| P4S/JJ | P4S/JJ/SIM | P4S/JJ/SIM/MMSIM01 |
| P4S/JJ | P4S/JJ/SIM | P4S/JJ/SIM/UAW000 |
| P4S/JJ | P4S/JJ/SIM | P4S/JJ/SIM/UWP001 |
| P4S/JJ | P4S/JJ/SIM | P4S/JJ/SIM/UWP002 |