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.

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,
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.



Your Answer

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

Browse other questions tagged or ask your own question.