Hierarchial Query

  • Dear all

    Following is my db table

    student_id student_code student_parent_id student_name

    1 11 0 a

    2 111 1 b

    3 1111 2 c

    4 11111 3 d

    Can anyone here please help me to generate following op?

    student_id student_code student_parent_id student_name Hierarchy

    1 11 0 a 11 - 111

    2 111 1 b 11-111-1111

    3 1111 2 c 11-111-1111-11111

    4 11111 3 d 11111

    Thanks

    Peter

  • Hi Peter,

    welcome to the forums. If you have a read of this [/url] article, it'll make it much easier to see what you want. All those 1's and dashes make it very difficult to see where you columns are.

    If I may say, what you're asking looks suspiciously like homework. People don't mind helping with homework but you'll find you're likely to get a better response if you post what you've already tried rather than just asking for the answer.

    Neil


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Hello there,

    I tried different options but not getting exactly what i am looking for..

    Kindly refer the attachment for the expected op

    Thanks

    Peter

  • Thanks Peter. Could you post the code you've tried please?


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • this is what i tried and not getting the expected op

    ;WITH SInfo AS

    (

    SELECTsId

    ,scode

    ,ParentId

    ,sName

    ,CONVERT(nvarchar(800), scode) AS Hierarchy

    FROMtest

    WHEREParentId=0

    UNIONALL

    SELECTTH.sId

    ,TH.scode

    ,TH.ParentId

    ,TH.sName

    ,CONVERT(nvarchar(800), (SInfo.Hierarchy +'\' + CONVERT(nvarchar(800), TH.scode)))

    FROMtest TH

    INNER JOINSInfoONSInfo.sId = TH.ParentId

    )

    Select * from SInfo

    thanks

    peter

  • Thanks again.

    Next question 🙂 Can you post your table definitions please? There are some column names in the code that don't match what you originally posted. And, looking at your code there's a backslash in there that doesn't appear in your expected outcome. I'm starting to see what you're trying to achieve but I don't want to make any assumptions and get it wrong.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • peterausger (8/29/2014)


    this is what i tried and not getting the expected op

    ;WITH SInfo AS

    (

    SELECTsId

    ,scode

    ,ParentId

    ,sName

    ,CONVERT(nvarchar(800), scode) AS Hierarchy

    FROMtest

    WHEREParentId=0

    UNIONALL

    SELECTTH.sId

    ,TH.scode

    ,TH.ParentId

    ,TH.sName

    ,CONVERT(nvarchar(800), (SInfo.Hierarchy +'\' + CONVERT(nvarchar(800), TH.scode)))

    FROMtest TH

    INNER JOINSInfoONSInfo.sId = TH.ParentId

    )

    Select * from SInfo

    thanks

    peter

    Peter,

    Post the data and the table as recommended in the first link under "helpful" links in my signature line below and I'm sure that someone will be able to answer your question in two shakes.

    --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)

  • Kindly find attached the table and the op required..

    Hope this info is good enough...

    Thanks

    Peter

  • peterausger (8/29/2014)


    Kindly find attached the table and the op required..

    Hope this info is good enough...

    Thanks

    Peter

    Graphics are copyable/pastable to SSMS. Do like the article says. Make the code and data readily consumable. 😉

    --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)

  • ---- create table

    create table test(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))

    ---- insert records

    insert into test values (1, '11', 0, 'a')

    insert into test values (2, '111', 1, 'b')

    insert into test values (3, '1111', 2, 'c')

    insert into test values (4, '11111', 3, 'd')

    ---- result query

    ;WITH SInfo AS

    (

    SELECTsId

    ,scode

    ,ParentId

    ,sName

    ,CONVERT(nvarchar(800), scode) AS Hierarchy

    FROMtest

    WHEREParentId=0

    UNIONALL

    SELECTTH.sId

    ,TH.scode

    ,TH.ParentId

    ,TH.sName

    ,CONVERT(nvarchar(800), (SInfo.Hierarchy +'\' + CONVERT(nvarchar(800), TH.scode)))

    FROMtest TH

    INNER JOINSInfoONSInfo.sId = TH.ParentId

    )

    Select * from SInfo

  • Crud. I have the answer but my connection from work won't let me post it. I'll see if I can attach it as a file.

    --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)

  • First, here's the result set from my solution for this problem...

    Results:

    sId scode ParentID sName Hierarchy

    --- ----- -------- ----- -------------------

    1 11 0 a 11\111

    2 111 1 b 11\111\1111

    3 1111 2 c 11\111\1111\11111

    4 11111 3 d 11111

    (4 row(s) affected)

    --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)

  • See attached for the SQL Code.

    --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)

  • Ok... your turn. Why did you need to have the Hierarchy column so that each row contained the next child and the leaf levels of the hierarchy only contained the child ID? Why the deviation from the normal expanded hierarchical order?

    --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)

  • This is more for fun than being an attempt to solve the problem, although the code can easily be amended to produce the desired results. It uses an inline Tally table and FOR XML to build the Hierarchy path.

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    IF OBJECT_ID('dbo.test') IS NULL

    BEGIN

    ---- create table

    create table dbo.test(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))

    ---- insert records

    insert into dbo.test values

    (1, '11', 0, 'a')

    ,(2, '111', 1, 'b')

    ,(3, '1111', 2, 'c')

    ,(4, '11111', 3, 'd');

    END

    --SET STATISTICS IO ON;

    ;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL)

    ,(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

    --sId scode ParentID sName Hierarchy

    SELECT

    T.sid

    ,T.scode

    ,T.parentid

    ,T.sname

    ,(( SELECT

    TP.scode + '\'

    FROM dbo.test TT

    OUTER APPLY

    (

    SELECT TOP((TT.parentid) + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS N FROM

    T T1,T T2,T T3,T T4,T T5,T T6,T T7

    ) AS NM(N)

    LEFT OUTER JOIN dbo.test TP

    ON NM.N = TP.sid

    WHERE T.sid = TT.sid

    FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(MAX)') + T.scode

    ) AS Hierarchy

    FROM dbo.test T

    --SET STATISTICS IO OFF;

    Results

    sid scode parentid sname Hierarchy

    ---- ------ --------- ------ ------------------

    1 11 0 a 11

    2 111 1 b 11\111

    3 1111 2 c 11\111\1111

    4 11111 3 d 11\111\1111\11111

Viewing 15 posts - 1 through 15 (of 17 total)

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