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 |
sampledata, not real data. Something that can represent what you are trying to do. Most people aren't going to put in the effort to create tables and dummy data so they can volunteer their time to help somebody else when that person doesn't put in the effort to make it easier.