--recursive queryIF object_id('Digui','U') is not NULL DROP TABLEDiguiCREATE TABLEDigui (IdVARCHAR( -), ParentIDVARCHAR( -))INSERT intodbo. Digui (Id, ParentID)Select 'Third Floor','First Floor'Union Select 'Second Floor','First Floor'Union Select 'Fourth Floor','First Floor'Union Select 'Tenth Floor','Second Floor'Union Select 'Nineth Floor','Second Floor'Union Select 'Eighth Floor','Fifth Floor'Union Select '20th Floor','Fourth Floor'Union Select '22nd Floor','Fourth Floor'Union Select '30th Floor','20th Floor'Union Select '32nd Floor','20th Floor'Union Select '40th Floor','30th Floor'Union Select '42nd Floor','30th Floor'Union Select '50th Floor','40th Floor'Union Select '52nd Floor','40th Floor'Union Select '80th Floor','50th Floor'Union Select '82nd Floor','50th Floor'Union Select '100th Floor','90th Floor'Union Select '102th Floor','90th Floor'; with Temp as(SelectId, ParentID fromDiguiwhere [ParentID] = 'First Floor'Union AllSelecta.id, A.parentid fromDigui aInner Join Temp onA.[ParentID] = Temp.[Id])Select * from Temp
SQL Server's CTE implementation recursive query