Translate a table with parent / child hierarchy into a table with parent name in separate columns?

user_2717063 0
Viewed 275

I have table for Location information which contains "Name", "Code", "Parent" and "Name" field contains all Country, State, District, Taluk names in single field.

enter image description here

But I would like to view in below format.

enter image description here

I found some similar problem statement here How to create query from parent child hierarchy table but which looks little complex using XML approach and it shows Parent and Child code but not the name field, As per comment mentioned in the thread we can get results by JSON approach. It would be great if I get help using JSON approach.

parent-child sql-server sql
1 Answers

You can use a standard recursive CTE to build the hierarchy and desired path of Names. Then it is a small matter to parse || delimited path with a bit of JSON.

;with cteP as (
      Select Name
            ,Code
            ,Parent 
            ,PathID = string_escape(Name,'JSON')
      From   YourTable
      Where  Parent is Null
      Union  All
      Select r.Name
            ,Child  = r.Code
            ,Parent = r.Parent 
            ,PathID = p.PathID+'||'+string_escape(r.Name,'JSON')
      From   YourTable r
      Join   cteP p on r.Parent  = p.Code)
Select A.Name
      ,A.Code
      ,A.Parent
      ,Country  = JSON_VALUE(S,'$[0]')
      ,State    = JSON_VALUE(S,'$[1]')
      ,District = JSON_VALUE(S,'$[2]')
      ,Taluk    = JSON_VALUE(S,'$[3]')
 From  cteP A
 Cross Apply ( values ( '["'+replace(PathID,'||','","')+'"]' ) ) B(S)

Results

enter image description here

user_15700000
Related