CODEWITHSUNDEEP
sqlsql-serversql-server-2016hierarchical-data
Harry
Harry

Reputation: 13

Flatten sql hierarchical data

Is it possible to flatten a hierarchical data like below without knowing the root?

I am thinking of using the below to find the ultimate parent first and then using the recursive hierarchial cte to find the child nodes

select parent,COUNT(*) from test_le where parent!=child group by parent

but again I was wondering if its possible to do it directly

Input

parent,child,parent_catg,child_catg
A   A   FIN FIN
A   B   FIN FIN
B   B   FIN FIN
A   C   FIN FIN
B   C   FIN FIN
C   C   FIN FIN
B   D   FIN FIN
C   D   FIN FIN
E   E   OTH OTH
E   F   OTH OTH
A   G   FIN FIN
B   G   FIN FIN
A   H   FIN FIN
P   P   FIN FIN
P   Q   FIN FIN

Desired Output

ultimate_parent child_nodes
A   B,C,D,G,H
E   F
P   Q

Thanks in advance

Upvotes: 1

Views: 1112

Answers (1)

Philip Kelley
Philip Kelley

Reputation: 40289

In SQL Server, using a CTE (Common Table Expression) pretty much is "how to access it directly". I loaded your data and attempted to work one up (test code below), but quickly hit a number of issues with your data...

  • Having the "self-identifying" rows (A,A) (B,B),etc. made things much more complex
  • As did not having all of them (there's no (D,D)

Perhaps more relevant (and I am not sure of the technical terms), but this is not a conventional hierarchy. D is a child of both B and C; and B is a child of A, with C a child of both B and A. As such, "conventional" means of walking through hierarchies will not apply.

My coding attempt is below as a start on addressing the problem, but it will not work given the nature of the data provided.

--CREATE TABLE #MyTable
-- (
--   Parent       char(1)  not null
--  ,Child        char(1)  not null
--  ,Parent_catg  char(3)  not null
--  ,Dhild_catg   char(3)  not null
-- )

--INSERT #MyTable values
--  ('A', 'A', 'FIN', 'FIN')
-- ,('A', 'B', 'FIN', 'FIN')
-- ,('B', 'B', 'FIN', 'FIN')
-- ,('A', 'C', 'FIN', 'FIN')
-- ,('B', 'C', 'FIN', 'FIN')
-- ,('C', 'C', 'FIN', 'FIN')
-- ,('B', 'D', 'FIN', 'FIN')
-- ,('C', 'D', 'FIN', 'FIN')
-- ,('E', 'E', 'OTH', 'OTH')
-- ,('E', 'F', 'OTH', 'OTH')
-- ,('A', 'G', 'FIN', 'FIN')
-- ,('B', 'G', 'FIN', 'FIN')
-- ,('A', 'H', 'FIN', 'FIN')
-- ,('P', 'P', 'FIN', 'FIN')
-- ,('P', 'Q', 'FIN', 'FIN')

;WITH cte
 as (

    select
       Parent
      ,Child
     from #MyTable
     where Child <> Parent
      and  Parent not in(select Child from #MyTable where Parent <> Child)

    union all select
       mt.Parent
      ,mt.Child
    from cte
     inner join #MyTable  mt
      on mt.Child = cte.Parent
    where mt.Child <> mt.Parent
     and cte.Child <> cte.Parent
    )

--select *
-- from cte
-- where Child <> Parent
-- order by Parent, Child

select
   Parent
  ,string_agg(Child, ',')
 from cte
 where Child <> Parent
 group by
   Parent

Upvotes: 2

Related Questions