Recursion in T-SQL

  • I have a table XYZ with following data..

    create table XYZ(parent_node_id int, child_id int)

    insert into XYZ select 0000000000,0000000001

    insert into XYZ select 0000000000,0000000124

    insert into XYZ select 0000000000,0000007953

    insert into XYZ select 0000007953,0000007954

    insert into XYZ select 0000007954,0000007955

    insert into XYZ select 0000007954,0000007956

    insert into XYZ select 0000007957,0000007958

    insert into XYZ select 0000007958,0000007959

    insert into XYZ select 0000007958,0000007960

    insert into XYZ select 0000007958,0000007961

    The result should be

    0000000000

    ------------0000000001

    ------------0000000124

    ------------0000007953

    ------------------------0000007954

    ------------------------------------0000007955

    ------------------------------------0000007956

    0000007953

    ------------0000007954

    0000007954

    ------------0000007955

    ------------0000007956

    0000007957

    ------------0000007958

    ------------------------0000007959

    ------------------------0000007960

    ------------------------0000007961

    0000007958

    ------------0000007959

    ------------0000007960

    ------------0000007961

    I hope you are getting my question..

    Its a recursive result where we first start with first parent_id..we select its child_id..then we make those child id as parent id and try to find their own child..

    I think this can be done by recursive CTE..but I am not able to figure it out..can you guys please help me here..Thanks in advance

  • Take a look at the first link in my signature line below and post some readily consumable data THAT way, someone will likely jump on this right away.

    The examples for "recursive CTE's" in Books Online are quite simple, though. All you'd really need to do is substitute a table name and a couple of column names. Try it. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (7/22/2011)


    Take a look at the first link in my signature line below and post some readily consumable data THAT way, someone will likely jump on this right away.

    The examples for "recursive CTE's" in Books Online are quite simple, though. All you'd really need to do is substitute a table name and a couple of column names. Try it. 😉

    Agreed on all accounts;-)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • @rockstar283,

    According to the links SwePeso provided, you already received an answer a half hour after you posted. It would be the right thing to do if you let people know so they don't waste the time solving a problem that you already have an answer for. Personally, it makes me think twice about answering any of your future questions. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you guys for your help and Jeff Moden for ultra-extraordinary explanation of the basics of asking questions in forums..I was completely unaware of that..That was really helpful..I appreciate that..was away from Internet..so couldnt mark the answer..Thank you SwePaso for wonderful help..

  • i just took a chance

    create table XYZ(parent_node_id int, child_id int)

    insert into XYZ select 0000000000,0000000001

    insert into XYZ select 0000000000,0000000124

    insert into XYZ select 0000000000,0000007953

    insert into XYZ select 0000007953,0000007954

    insert into XYZ select 0000007954,0000007955

    insert into XYZ select 0000007954,0000007956

    insert into XYZ select 0000007957,0000007958

    insert into XYZ select 0000007958,0000007959

    insert into XYZ select 0000007958,0000007960

    insert into XYZ select 0000007958,0000007961

    ALTER PROC dbo.Show_Hierarchy

    (

    @Root int

    )

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @child_id int

    SET @child_id = (SELECT child_id FROM dbo.XYZ WHERE child_id = @Root)

    PRINT REPLICATE('-', @@NESTLEVEL * 4) + CAST(@child_id AS VARCHAR(50))

    SET @child_id = (SELECT MIN(child_id) FROM dbo.XYZ WHERE parent_node_id = @Root)

    WHILE @child_id IS NOT NULL

    BEGIN

    EXEC dbo.Show_Hierarchy @child_id

    SET @child_id = (SELECT MIN(child_id) FROM dbo.XYZ WHERE parent_node_id = @Root AND child_id > @child_id)

    END

    END

    GO

    --EXEC dbo.Show_Hierarchy 0000000000

    GO

  • ghanshyam.kundu (7/25/2011)


    i just took a chance

    Interesting way to do the same as Celko's infamous "push stack" conversion. Thanks for posting it.

    I have code that will generate a million node "pure hierarchy" and, when I get a chance, I'll see what the performance might like.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply