SQL Server Performance

Parent Child Relation Ship

Discussion in 'SQL Server 2008 General Developer Questions' started by Brinda, Dec 13, 2010.

  1. Brinda New Member

    Hi ,
    I have a Table as below,
    ID Column1 Column2 Column3
    1 ABC ZXC AAA
    2 123 ASDF BBB
    3 DERT ASDER ASDF
    And I need the output like this
    ID PARENTID HEADERTEXT
    1 NULL ABC
    2 NULL 123
    3 NULL DERT
    4 1 ZXC
    5 2 ASDF
    6 3 ASDER
    7 4 AAA
    8 5 BBB
    9 6 ASDF
    Brinda, Dec 13, 2010
    #2

  2. Adriaan New Member

    Any reason why your table design is not simply in the format of the required output?
    Adriaan, Dec 13, 2010
    #3

  3. FrankKalis Moderator

    Apart from Adriaan's very valid question, here is an approach that works with you sample data. It may or may not work with your real data:
    DECLARE @T TABLE (
    ID int,
    Column1 varchar(10),
    Column2 varchar(10),
    Column3 varchar(10)
    );

    INSERT INTO @T
    (ID, Column1, Column2, Column3)
    SELECT 1, 'ABC', 'ZXC', 'AAA' UNION ALL
    SELECT 2, '123','ASDF','BBB' UNION ALL
    SELECT 3,'DERT','ASDER','ASDF'

    SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID,
    CASE
    WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - (SELECT COUNT(*) FROM @T) <= 0
    THEN NULL
    ELSE ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - (SELECT COUNT(*) FROM @T)
    END AS ParentID,
    T.HeaderText
    FROM
    (SELECT TOP 100 PERCENT
    ID,
    Column1 AS HeaderText
    FROM
    @T T
    ORDER BY
    T.ID
    UNION ALL
    SELECT TOP 100 PERCENT
    ID,
    Column2
    FROM
    @T T
    ORDER BY
    T.ID
    UNION ALL
    SELECT TOP 100 PERCENT
    ID,
    Column3
    FROM
    @T T
    ORDER BY
    T.ID) T;
    ID ParentID HeaderText
    1 NULL ABC
    2 NULL 123
    3 NULL DERT
    4 1 ZXC
    5 2 ASDF
    6 3 ASDER
    7 4 AAA
    8 5 BBB
    9 6 ASDF

    (9 row(s) affected)
    If possible, I would really fix that design.
    FrankKalis, Dec 13, 2010
    #4

  4. Brinda New Member

    Hi FrankKalis,
    Thanks a lot but i need a favor.
    If I have one more column
    ID Column1 Column2 Column3 Quantity
    1 ABC ZXC AAA 1
    1 ABC ZXC CCC 2
    2 123 ASDF BBB 32
    3 DERT ASDER ASDF 4
    i have group by the revalent things so that i should get the result as
    ID PARENTID HEADERTEXT Count
    1 NULL ABC 3
    2 NULL 123 32
    3 NULL DERT 4
    4 1 ZXC 1
    5 2 ASDF 32
    6 3 ASDER 4
    7 4 AAA 1
    8 5 BBB 32
    9 6 ASDF 4
    Brinda, Dec 13, 2010
    #5

  5. FrankKalis Moderator

    This is what I meant before, that it works with your sample data, but may fail with your real data. Now you introduce a new column and a new row and I honestly have no idea what Quantity relates to and what it should mean at all. I think your whole requirement is more than vague.
    Could you please try to explain what your sample data stands for and why it should be presented in the way it is?
    FrankKalis, Dec 14, 2010
    #6

  6. Brinda New Member

    Hi,
    My Requirement is that,I need to display the data it in hierarchical form i.e I want to bind it to the treeview.so that my treeview look like this
    + ABC 3
    + ZXC 3
    + AAA 1
    + CCC 2
    + 123 32
    + ASDF 32
    + BBB 32
    + DERT 4
    + ASDER 4
    +ASDF 4
    From single I am not able to do the query formation
    Since the column is chosen by the user, it’s a dynamic one. Either column2 is child of column1 or column3 is child of column1, so that I am not able to frame the query.
    I Just want ID & parentID column so that i will pass this to my control(Treeview)
    Is the explanation is enough or u want it elaborately
    Brinda, Dec 14, 2010
    #7

  7. FrankKalis Moderator

    Yes, that makes it clearer. Would this help you?
    DECLARE @T TABLE (
    ID int,
    Column1 varchar(10),
    Column2 varchar(10),
    Column3 varchar(10),
    Quantity int
    );

    INSERT INTO @T
    (ID, Column1, Column2, Column3, Quantity)
    SELECT 1, 'ABC', 'ZXC', 'AAA', 1 UNION ALL
    SELECT 1,'ABC','ZXC','CCC', 2 UNION ALL
    SELECT 2, '123','ASDF','BBB', 32 UNION ALL
    SELECT 3,'DERT','ASDER','ASDF', 4

    SELECT
    foo.Lvl,
    foo.HeaderText,
    SUM(foo.Quantity) AS [Count],
    MAX(foo.ID) AS ID
    FROM
    (SELECT
    1 AS Lvl, T.ID, Column1 AS HeaderText, T.Quantity
    FROM
    @T T
    UNION ALL
    SELECT
    2 AS Lvl, T.ID, Column2 AS HeaderText, T.Quantity
    FROM
    @T T
    UNION ALL
    SELECT
    3 AS Lvl, T.ID, T.Column3 AS HeaderText, T.Quantity
    FROM
    @T T
    ) foo
    GROUP BY
    foo.Lvl, foo.HeaderText
    ORDER BY
    foo.Lvl, ID;
    Lvl HeaderText Count ID
    1 ABC 3 1
    1 123 32 2
    1 DERT 4 3
    2 ZXC 3 1
    2 ASDF 32 2
    2 ASDER 4 3
    3 AAA 1 1
    3 CCC 2 1
    3 BBB 32 2
    3 ASDF 4 3

    (10 row(s) affected)

    FrankKalis, Dec 14, 2010
    #8

  8. Brinda New Member

    Nope FrankKalis,
    I Dont want the level index,i want as like the previous result of wt u hv given(Parent-Child). I am running out of time FrankKalis [:(]
    Brinda, Dec 14, 2010
    #9

Share This Page