0

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
Geethu
  • 55
  • 11
  • Table definitions and sample data would make this a LOT more clear. – Sean Lange Aug 19 '22 at 20:01
  • @SeanLange: I have to extract all the Child elements of a Parent and have it against the Parent element until there are are no more Child elements for a Parent. Sorry, I won't be able to post sample data here – Geethu Aug 19 '22 at 20:09
  • With no table definitions and no sample data nobody can help. Notice I said `sample` data, 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. – Sean Lange Aug 19 '22 at 20:13
  • @SeanLange I have added the sample data . Please let me know will this suffice? – Geethu Aug 19 '22 at 21:08
  • What datatype is this? This data is so confusing, there are multiple values on each side. How can you know which P4S/JJ one of the child rows belongs to? – Sean Lange Aug 19 '22 at 21:17
  • @SeanLange This is a raw data imported from excel file where the data is not clean. Because of which even I am facing the challenge to filter out and finding the child nodes – Geethu Aug 19 '22 at 21:27
  • So if you can't determine what is correct with your data how can you expect somebody to get it right? Not trying to sound negative, but this is just entirely too vague. – Sean Lange Aug 22 '22 at 03:50

0 Answers0