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
7
  • Table definitions and sample data would make this a LOT more clear. Commented Aug 19, 2022 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 Commented Aug 19, 2022 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. Commented Aug 19, 2022 at 20:13
  • @SeanLange I have added the sample data . Please let me know will this suffice? Commented Aug 19, 2022 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? Commented Aug 19, 2022 at 21:17

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.