none
Parent Child Relationship RRS feed

  • Question

  • Hello Team,

    I have below data in my database. I need three more columns in my output those are level1,level2 and level3. The final output should be like second table below. 

    Can you please let me know the sql query to get final output?

    Actual Data in my table

    ID ParentID Name
    0 0 Unknown
    2 0 Europe Region
    108 2 MBFS Europe Region
    5 108 UK
    9 108 Austria
    17 108 Russia
    22 108 BeNeLux
    32 108 France
    36 108 Italy
    41 108 Poland
    78 108 Germany
    79 108 Spain
    80 108 Croatia
    81 108 Greece
    82 108 Hungary
    83 108 Portugal
    84 108 Romania
    85 108 Slovenia
    86 108 Czech Republic
    87 108 Slovakia
    88 108 Denmark
    89 108 Sweden
    90 108 Switzerland
    91 108 Turkey
    140 108 Friesland

    Need Final Output

    ID ParentID Name Level1 Level2 Level3
    0 0 Unknown Unknown Unknown Unknown
    2 0 Europe Region Europe Region Unknown Unknown
    108 2 MBFS Europe Region Europe Region Unknown Unknown
    5 108 UK Europe Region MBFS Europe Region UK
    9 108 Austria Europe Region MBFS Europe Region Austria
    17 108 Russia Europe Region MBFS Europe Region Russia
    22 108 BeNeLux Europe Region MBFS Europe Region BeNeLux
    32 108 France Europe Region MBFS Europe Region France
    36 108 Italy Europe Region MBFS Europe Region Italy
    41 108 Poland Europe Region MBFS Europe Region Poland
    78 108 Germany Europe Region MBFS Europe Region Germany
    79 108 Spain Europe Region MBFS Europe Region Spain
    80 108 Croatia Europe Region MBFS Europe Region Croatia
    81 108 Greece Europe Region MBFS Europe Region Greece
    82 108 Hungary Europe Region MBFS Europe Region Hungary
    83 108 Portugal Europe Region MBFS Europe Region Portugal
    84 108 Romania Europe Region MBFS Europe Region Romania
    85 108 Slovenia Europe Region MBFS Europe Region Slovenia
    86 108 Czech Republic Europe Region MBFS Europe Region Czech Republic
    87 108 Slovakia Europe Region MBFS Europe Region Slovakia
    88 108 Denmark Europe Region MBFS Europe Region Denmark
    89 108 Sweden Europe Region MBFS Europe Region Sweden
    90 108 Switzerland Europe Region MBFS Europe Region Switzerland
    91 108 Turkey Europe Region MBFS Europe Region Turkey
    140 108 Friesland Europe Region MBFS Europe Region Friesland

    Thanks,

    Kumar


    Anil

    Monday, January 20, 2020 8:41 AM

All replies

  • But for 108 it should be produce ,no?

    lvl1                                                            lvl2                                    lvl3

    MBFS Europe Region Europe Region Unknown


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Monday, January 20, 2020 12:22 PM
    Moderator
  • Maybe:

    ; WITH rekurs AS (
       SELECT ID, ParentID, Name, Name AS Level1, Name AS Level2, Name AS Level3
       FROM   tbl
       WHERE  ID = ParentID
       UNION ALL
       SELECT t.ID, t.ParentID, t.Name, t.Name, r.Level1, 2.Level2
       FROM   tbl t
       JOIN   rekurs r ON t.ParentID = ID
    )
    SELECT ID, ParentID, Name, Level1, Level2, Level3
    FROM   rekurs

    Had you posted CREATE TABLE + INSERT statements for your table and data, I would have tested my solution, but you didn't.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, January 20, 2020 10:21 PM
  • Hi Anil Katabathuni,

    Please try following script.

    IF OBJECT_ID('test') IS NOT NULL drop table  test   
    go 
    create table test (
    ID int,
    ParentID int, 
    Name varchar(20)
    )
    insert into test values 
    (0	,0	,'Unknown'),
    (2	,0	,'Europe Region'),
    (108,2  ,'MBFS Europe Region'),
    (5	,108,'UK'),
    (9	,108,'Austria'),
    (17	,108,'Russia'),
    (22	,108,'BeNeLux'),
    (32	,108,'France'),
    (36	,108,'Italy'),
    (41	,108,'Poland'),
    (78	,108,'Germany'),
    (79	,108,'Spain'),
    (80	,108,'Croatia'),
    (81	,108,'Greece'),
    (82	,108,'Hungary'),
    (83	,108,'Portugal'),
    (84	,108,'Romania'),
    (85	,108,'Slovenia'),
    (86	,108,'Czech Republic'),
    (87	,108,'Slovakia'),
    (88	,108,'Denmark'),
    (89	,108,'Sweden'),
    (90	,108,'Switzerland'),
    (91	,108,'Turkey'),
    (140,108,'Friesland')
    
    
    ; WITH rekurs AS (
       SELECT ID, ParentID, Name, Name AS Level1, Name AS Level2, Name AS Level3,0 AS TreeLevel
       FROM   test
       WHERE  ID = ParentID
       UNION ALL
       SELECT t.ID, t.ParentID, t.Name, 
       case when r.Level1='Unknown' then t.Name else r.Level1 end , 
       case when r.Level2='Unknown' and r.Level1<>'Unknown' then t.Name else r.Level2 end ,
       case when r.Level3='Unknown'and r.Level1<>'Unknown'and r.Level2<>'Unknown' then t.Name else r.Level3 end ,
       TreeLevel + 1
       FROM   test t
       JOIN   rekurs r ON t.ParentID = r.ID and r.ID<>t.ID
    
    )
    SELECT ID, ParentID, Name, Level1, Level2, Level3
    FROM   rekurs option (maxrecursion 0)
    /*
    ID          ParentID    Name                 Level1               Level2               Level3
    ----------- ----------- -------------------- -------------------- -------------------- --------------------
    0           0           Unknown              Unknown              Unknown              Unknown
    2           0           Europe Region        Europe Region        Unknown              Unknown
    108         2           MBFS Europe Region   Europe Region        MBFS Europe Region   Unknown
    5           108         UK                   Europe Region        MBFS Europe Region   UK
    9           108         Austria              Europe Region        MBFS Europe Region   Austria
    17          108         Russia               Europe Region        MBFS Europe Region   Russia
    22          108         BeNeLux              Europe Region        MBFS Europe Region   BeNeLux
    32          108         France               Europe Region        MBFS Europe Region   France
    36          108         Italy                Europe Region        MBFS Europe Region   Italy
    41          108         Poland               Europe Region        MBFS Europe Region   Poland
    78          108         Germany              Europe Region        MBFS Europe Region   Germany
    79          108         Spain                Europe Region        MBFS Europe Region   Spain
    80          108         Croatia              Europe Region        MBFS Europe Region   Croatia
    81          108         Greece               Europe Region        MBFS Europe Region   Greece
    82          108         Hungary              Europe Region        MBFS Europe Region   Hungary
    83          108         Portugal             Europe Region        MBFS Europe Region   Portugal
    84          108         Romania              Europe Region        MBFS Europe Region   Romania
    85          108         Slovenia             Europe Region        MBFS Europe Region   Slovenia
    86          108         Czech Republic       Europe Region        MBFS Europe Region   Czech Republic
    87          108         Slovakia             Europe Region        MBFS Europe Region   Slovakia
    88          108         Denmark              Europe Region        MBFS Europe Region   Denmark
    89          108         Sweden               Europe Region        MBFS Europe Region   Sweden
    90          108         Switzerland          Europe Region        MBFS Europe Region   Switzerland
    91          108         Turkey               Europe Region        MBFS Europe Region   Turkey
    140         108         Friesland            Europe Region        MBFS Europe Region   Friesland
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 21, 2020 8:07 AM