I have this script that enables me to extract hierarchies and the description for each level however the number of levels has to be known beforehand to define the number of select queries necessary for the CTE.
I want to change this by making this query simulate a loop after getting the count of the levels in each table.
with niv1_2 as (select distinct [parent] as niv1, child as niv2, [Value] as description2 FROM [dbo].[stgaccount] WHERE [parent] LIKE '#root' ), niv2_3 as (select niv1 , niv2, per.child as niv3,description2, [Value] as description3 from niv1_2 n12 left join [dbo].[stgaccount] per on n12.niv2 = per.[parent] ) SELECT LTRIM(RTRIM(niv1)) niv1 , LTRIM(RTRIM(niv2)) niv2, LTRIM(RTRIM(niv3)) niv3, description2,description3 FROM niv2_3
I was thinking of using this query to get the number of levels:
DECLARE @levelnum int = 0; ;With cteOH (name, parent, Lvl) as ( Select name,Parent,Lvl=1 from pfedb.dbo.stgperiod where Parent like '#root' Union All Select h.name,h.Parent,cteOH.Lvl+1 FROM pfedb.dbo.stgperiod h INNER JOIN cteOH ON h.Parent = cteOH.name ) Select @levelnum= max(Lvl) From cteOH
I'm not quite sure on how to change the original script to make it more generic.