Flat table for parent child

  • Hi,

    I want to create a flat table as shown in the desired results using the temp table.

    CREATE TABLE #MyTestTable

    (

    Title NVARCHAR(200),

    ID NVARCHAR(100),

    ChildID NVARCHAR(650)

    )

    INSERT INTO #MyTestTable ( Title,ID,ChildID)

    SELECT 'Root','IEABEL6II7777777','XY0000000KIE'

    UNION

    SELECT 'Root Project','XY0000000KIE','XY0000000KIA'

    UNION

    SELECT 'Root Project','XY0000000KIE','XY0000000KIB'

    UNION

    SELECT 'Sales Workflow','XY0000000KIA','XY0000000LLA'

    UNION

    SELECT 'Sales Workflow','XY0000000KIA','XY0000000LLB'

    UNION

    SELECT 'Sales Project1','XY0000000LLA','XY0000000LLAX'

    UNION

    SELECT 'Sales Project2','XY0000000LLB','XY0000000LLAY'

    UNION

    SELECT 'Sales SubProject1','XY0000000LLAX','XY0000000LLAX-1'

    UNION

    SELECT 'Sales Attribute1','XY0000000LLAX-1',''

    UNION

    SELECT 'Sales SubProject2','XY0000000LLAY','XY0000000LLAD'

    UNION

    SELECT 'Sales Attribute2','XY0000000LLAD','XY0000000LLAD-1'

    UNION

    SELECT 'Sales Attribute1','XY0000000LLAX-1',''

    UNION

    SELECT 'Technology Workflow','XY0000000KIB','XY0000000TECH1'

    UNION

    SELECT 'Technology Project1','XY0000000TECH1','XY0000000SUB1'

    UNION

    SELECT 'Technology SubProject1','XY0000000SUB1','XY0000000ATTR1'

    UNION

    SELECT 'Technology Attribute1','XY0000000ATTR1',''

    I need a flat table with structure like below using above temp table

    --Desired results only from (Root Project) only

    SELECT 'Root Project' AS ProjectRoot,'XY0000000KIE' AS ParentID,'XY0000000KIA' AS WorkFlowID , 'Sales Workflow' As Workflow,'Sales Project1' AS Project ,'XY0000000LLA' AS ProjectID,'XY0000000LLAX' AS SubProjectID,'Sales SubProject1' AS SubProjects,'XY0000000LLAX-1' AS AttributeID,'Sales Attribute1' AS Attributes

    DROP TABLE #MyTestTable

    Please help.

  • Great job posting ddl, sample data and desired output. What I don't understand at all is how you get from your data to the output. The column names don't seem to help and you have lots of rows with the contents for the values. It seems like a sort of dynamic cross tab is needed here but I can't make heads or tails of the output.

    _______________________________________________________________

    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/

  • Something like this?

    SELECT

    pr.Title AS ProjectRoot , pr.ID AS ParentID,

    wf.Title AS WorkFlow , wf.ID AS WorkflowID,

    p.Title AS Project , p.ID AS ProjectID,

    sp.Title AS SubProject , sp.ID AS SubProjectsID,

    pa.Title AS Attribute , pa.ID AS AttributesID

    FROM #MyTestTable r

    JOIN #MyTestTable pr ON r.ChildID = pr.ID

    JOIN #MyTestTable wf ON pr.ChildID = wf.ID

    JOIN #MyTestTable p ON wf.ChildID = p.ID

    JOIN #MyTestTable sp ON p.ChildID = sp.ID

    JOIN #MyTestTable pa ON sp.ChildID = pa.ID

    --WHERE SomeCondition = @SomeArgument

    ;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks Luis. It work perfectly .

    If I have to add another level (Attribute2 and AttributeID2) how can I achieve it ?

    I tried adding JOIN #MyTestTable pa2 ON sp.ChildID = pa2.ID to the end of the join but it didn't work .

  • Luis Cazares (1/6/2017)


    Something like this?

    SELECT

    pr.Title AS ProjectRoot , pr.ID AS ParentID,

    wf.Title AS WorkFlow , wf.ID AS WorkflowID,

    p.Title AS Project , p.ID AS ProjectID,

    sp.Title AS SubProject , sp.ID AS SubProjectsID,

    pa.Title AS Attribute , pa.ID AS AttributesID

    FROM #MyTestTable r

    JOIN #MyTestTable pr ON r.ChildID = pr.ID

    JOIN #MyTestTable wf ON pr.ChildID = wf.ID

    JOIN #MyTestTable p ON wf.ChildID = p.ID

    JOIN #MyTestTable sp ON p.ChildID = sp.ID

    JOIN #MyTestTable pa ON sp.ChildID = pa.ID

    --WHERE SomeCondition = @SomeArgument

    ;

    Well done sir. You win the internet today....or perhaps we know how has the crystal ball this week. 😀

    _______________________________________________________________

    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/

  • I could it get it working ..

    SELECT

    pr.Title AS ProjectRoot , pr.ID AS ParentID,

    wf.Title AS WorkFlow , wf.ID AS WorkflowID,

    p.Title AS Project , p.ID AS ProjectID,

    sp.Title AS SubProject , sp.ID AS SubProjectsID,

    pa.Title AS Attribute , pa.ID AS AttributesID,

    pa2.Title AS Attribute2 , pa2.ID AS AttributesID2

    FROM #MyTestTable r

    JOIN #MyTestTable pr ON r.ChildID = pr.ID

    JOIN #MyTestTable wf ON pr.ChildID = wf.ID

    JOIN #MyTestTable p ON wf.ChildID = p.ID

    JOIN #MyTestTable sp ON p.ChildID = sp.ID

    JOIN #MyTestTable pa ON sp.ChildID = pa.ID

    LEFT JOIN #MyTestTable pa2 ON pa.ChildID = pa2.ID

    Thanks for your help again . 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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