Manupalting ParentID in the table

  • I have table with data like this, which gives me a tree structure using parent ID.

    Seq ParentID Description

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

    1 0 Desc1

    2 1 Desc1

    3 2 Desc1

    4 2 Desc1

    5 4 Desc1

    6 4 Desc1

    7 4 Desc1

    8 1 Desc1

    I need to join another child table table to this table. What should happen is. It should resequnce the Seq field and adjust the parent ID.

    select

    seq ( resequnce ),

    parented ( resequnce ),

    description

    from table 1

    join table 2 on.....

  • skb 44459 (5/13/2014)


    I have table with data like this, which gives me a tree structure using parent ID.

    Seq ParentID Description

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

    1 0 Desc1

    2 1 Desc1

    3 2 Desc1

    4 2 Desc1

    5 4 Desc1

    6 4 Desc1

    7 4 Desc1

    8 1 Desc1

    I need to join another child table table to this table. What should happen is. It should resequnce the Seq field and adjust the parent ID.

    select

    seq ( resequnce ),

    parented ( resequnce ),

    description

    from table 1

    join table 2 on.....

    "resequnce the Seq field and adjust the parent ID." does not explain what should happen. We need to know what that means.

    In order to help we will need a few things:

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Here is the actual code. I was able to get the resequence using the ROW_NUMBER function. But now I need to get the Parent ID which will point to the Resequnced data.

    SELECT

    (ROW_NUMBER() OVER (ORDER BY seq)) AS 'dff'

    ,seq

    ,REPLACE(TcLevel,'.','*') AS 'Level'

    ,Tcparentid

    ,(REPLICATE('---',CAST( REPLACE(TcLevel,'.','') AS INT)) + TcItem ) AS 'Indented Item'

    ,TcItem

    ,TcDesc

    ,ki_qualplan.ref_type

    ,ki_qualplan.item

    ,ki_qualplan.item_descr

    ,ki_qualplan.entity

    ,ki_qualplan.sequence

    ,ki_qualplan.test_key

    ,ki_qualplan.sev_level

    ,ki_qualplan.test_name

    ,ki_qualplan.expected_min

    ,ki_qualplan.expected_nominal

    ,ki_qualplan.expected_max

    ,ki_qualplan.qcmethod

    ,ki_qualplan.characteristic

    ,ki_qualplan.charfld2

    ,(SELECT Uf_QCTestDetails FROM rs_qcitemd WHERE rs_qcitemd.item = ki_qualplan.item AND rs_qcitemd.ref_type = ki_qualplan.ref_type AND rs_qcitemd.entity = ki_qualplan.entity AND rs_qcitemd.test_seq = ki_qualplan.test_seq AND rs_qcitemd.sequence = ki_qualplan.sequence) AS 'qcdetails'

    FROM #reportsetQual

    left OUTER JOIN ki_qualplan ON TcItem = ki_qualplan.item

    ORDER BY seq

  • skb 44459 (5/13/2014)


    Here is the actual code. I was able to get the resequence using the ROW_NUMBER function. But now I need to get the Parent ID which will point to the Resequnced data.

    SELECT

    (ROW_NUMBER() OVER (ORDER BY seq)) AS 'dff'

    ,seq

    ,REPLACE(TcLevel,'.','*') AS 'Level'

    ,Tcparentid

    ,(REPLICATE('---',CAST( REPLACE(TcLevel,'.','') AS INT)) + TcItem ) AS 'Indented Item'

    ,TcItem

    ,TcDesc

    ,ki_qualplan.ref_type

    ,ki_qualplan.item

    ,ki_qualplan.item_descr

    ,ki_qualplan.entity

    ,ki_qualplan.sequence

    ,ki_qualplan.test_key

    ,ki_qualplan.sev_level

    ,ki_qualplan.test_name

    ,ki_qualplan.expected_min

    ,ki_qualplan.expected_nominal

    ,ki_qualplan.expected_max

    ,ki_qualplan.qcmethod

    ,ki_qualplan.characteristic

    ,ki_qualplan.charfld2

    ,(SELECT Uf_QCTestDetails FROM rs_qcitemd WHERE rs_qcitemd.item = ki_qualplan.item AND rs_qcitemd.ref_type = ki_qualplan.ref_type AND rs_qcitemd.entity = ki_qualplan.entity AND rs_qcitemd.test_seq = ki_qualplan.test_seq AND rs_qcitemd.sequence = ki_qualplan.sequence) AS 'qcdetails'

    FROM #reportsetQual

    left OUTER JOIN ki_qualplan ON TcItem = ki_qualplan.item

    ORDER BY seq

    Consider that I don't have these tables, I can't see your screen and I have no idea what you are trying to do. Maybe you need to join to the original table again? Without something to work with and desired output it is just guess work.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 4 posts - 1 through 3 (of 3 total)

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