none
Top-pest parent column should be as new column

    Question

  • Hi,

    i have a table like below 

    CREATE TABLE #MyTable (ID Integer,Description varchar(200),ParentID Integer)
    insert INTO #MyTable (ID,Description,ParentID) VALUES (1,'1AAA',0)
    insert INTO #MyTable (ID,Description,ParentID) VALUES (2,'1BBB',0)
    insert INTO #MyTable (ID,Description,ParentID) VALUES (3,'2AAA',1)
    insert INTO #MyTable (ID,Description,ParentID) VALUES (4,'2BBB',2)
    insert INTO #MyTable (ID,Description,ParentID) VALUES (5,'3AAA',3)
    insert INTO #MyTable (ID,Description,ParentID) VALUES (6,'3AAB',3)
    insert INTO #MyTable (ID,Description,ParentID) VALUES (7,'3BBB',4)
    insert INTO #MyTable (ID,Description,ParentID) VALUES (8,'3BBC',4)
    SELECT * from #MyTable

    I need a new column which has to display the top-pest parent. like below

    Thanks

    Saturday, August 09, 2014 10:53 AM

Answers

  • Hi,

    your result which displays is wrong. i have pasted my result screen below

    hope you have understand the screen shot

    Thanks

    Try below

    select a.ID,a.Description,a.ParentID,case when b.parentid =0 then a.parentid else isnull(b.parentid,0) end Topid From #MyTable a 
    	Left outer join #MyTable b on a.ParentID=b.ID

    Thanks

    Saravana Kumar C

    Monday, August 11, 2014 6:07 AM
  • I think you are looking for the below:

    Added one row for one more level for you tio understand and cross check the output for ID "9".

    CREATE TABLE #MyTable (ID Integer,Description varchar(200),ParentID Integer) insert INTO #MyTable (ID,Description,ParentID) VALUES (1,'1AAA',0) insert INTO #MyTable (ID,Description,ParentID) VALUES (2,'1BBB',0) insert INTO #MyTable (ID,Description,ParentID) VALUES (3,'2AAA',1) insert INTO #MyTable (ID,Description,ParentID) VALUES (4,'2BBB',2) insert INTO #MyTable (ID,Description,ParentID) VALUES (5,'3AAA',3) insert INTO #MyTable (ID,Description,ParentID) VALUES (6,'3AAB',3) insert INTO #MyTable (ID,Description,ParentID) VALUES (7,'3BBB',4) insert INTO #MyTable (ID,Description,ParentID) VALUES (8,'3BBC',4) insert INTO #MyTable (ID,Description,ParentID) VALUES (9,'3BBC',8) SELECT * from #MyTable ;With cte as ( Select ID,Description,ParentID OriginalParentID,ParentID,ParentID as TOPID From #MyTable Where ParentID<>0 Union All Select B.ID,B.Description,B.OriginalParentID, A.ParentID, B.ParentID From #MyTable A Inner join cte B on A.ID = B.ParentID and B.ParentID <>0 ) Select ID,Description,ParentID, 0 as TopID From #MyTable Where ParentID =0 Union All Select ID,Description,OriginalParentID ParentID, TopId From cte where ParentID=0

    Order by ID asc Drop table #MyTable


    Monday, August 11, 2014 8:37 AM
  • Hi Thangam - Kasim

    OK, I get that you need the top ID :-) I see that you got 2 great solutions.

    Please make sure that you read  Visakh16 response, regarding the first solution.

    In any case, here is a bit faster solution.

    ;with MyCTE as (
    	select 
    		T1.ID, T1.Description,T1.ParentID, 0 as TopID
    	from #MyTable T1
    	where ParentID = 0
    
    	union all
    
    	select 
    		T1.ID,T1.Description,T1.ParentID, CASE when isnull(T2.TopID,0)=0 then T2.ID else T2.TopID end as TopID
    	from #MyTable T1
    	inner join MyCTE T2 on T1.ParentID = T2.ID
    )
    select * from MyCTE
    Order by ID asc
    I hope this is useful :-)


    [Personal Site] [Blog] [Facebook]signature

    Monday, August 11, 2014 12:19 PM
    Moderator

All replies

  • Can you try this;

    alter table #mytable Add TopID int;
    
    Update #MyTable Set TopID = ParentID
    Please Mark This As Answer if it helps to solve the issue


    • Edited by VINO-TH Saturday, August 09, 2014 11:54 AM Added update statement
    Saturday, August 09, 2014 11:13 AM
  • Hi,

    is this what you look for?

    If not then please explain the logic in the TopID values

    ;with MyCTE as (
    	select 
    		T1.ID, T1.Description,T1.ParentID, 0 as TopID
    	from #MyTable T1
    	where ParentID = 0
    
    	union all
    
    	select 
    		T1.ID,T1.Description,T1.ParentID, T2.TopID+1 as TopID
    	from #MyTable T1
    	inner join MyCTE T2 on T1.ParentID = T2.ID
    )
    select * from MyCTE


    [Personal Site] [Blog] [Facebook]signature

    Saturday, August 09, 2014 11:31 AM
    Moderator
  • Hi,

    your result which displays is wrong. i have pasted my result screen below

    hope you have understand the screen shot

    Thanks

    Monday, August 11, 2014 5:45 AM
  • Hi,

    your result which displays is wrong. i have pasted my result screen below

    hope you have understand the screen shot

    Thanks

    Try below

    select a.ID,a.Description,a.ParentID,case when b.parentid =0 then a.parentid else isnull(b.parentid,0) end Topid From #MyTable a 
    	Left outer join #MyTable b on a.ParentID=b.ID

    Thanks

    Saravana Kumar C

    Monday, August 11, 2014 6:07 AM
  • Hi,

    Try this..

    SELECT a.*,
            ISNULL(b.ParentID,a.ParentID) [TopParentID]
    FROM #MyTable a
    LEFT JOIN #MyTable b ON b.ID = a.ParentID

    Regards,

    Brindha.

    Monday, August 11, 2014 8:15 AM
  • I think you are looking for the below:

    Added one row for one more level for you tio understand and cross check the output for ID "9".

    CREATE TABLE #MyTable (ID Integer,Description varchar(200),ParentID Integer) insert INTO #MyTable (ID,Description,ParentID) VALUES (1,'1AAA',0) insert INTO #MyTable (ID,Description,ParentID) VALUES (2,'1BBB',0) insert INTO #MyTable (ID,Description,ParentID) VALUES (3,'2AAA',1) insert INTO #MyTable (ID,Description,ParentID) VALUES (4,'2BBB',2) insert INTO #MyTable (ID,Description,ParentID) VALUES (5,'3AAA',3) insert INTO #MyTable (ID,Description,ParentID) VALUES (6,'3AAB',3) insert INTO #MyTable (ID,Description,ParentID) VALUES (7,'3BBB',4) insert INTO #MyTable (ID,Description,ParentID) VALUES (8,'3BBC',4) insert INTO #MyTable (ID,Description,ParentID) VALUES (9,'3BBC',8) SELECT * from #MyTable ;With cte as ( Select ID,Description,ParentID OriginalParentID,ParentID,ParentID as TOPID From #MyTable Where ParentID<>0 Union All Select B.ID,B.Description,B.OriginalParentID, A.ParentID, B.ParentID From #MyTable A Inner join cte B on A.ID = B.ParentID and B.ParentID <>0 ) Select ID,Description,ParentID, 0 as TopID From #MyTable Where ParentID =0 Union All Select ID,Description,OriginalParentID ParentID, TopId From cte where ParentID=0

    Order by ID asc Drop table #MyTable


    Monday, August 11, 2014 8:37 AM
  • Thanks its working.
    Monday, August 11, 2014 8:51 AM
  • The above query its not working. bcoz TopParendtID not displaying for ID 3,4.

    any way the below query is working fine.

    select a.ID,a.Description,a.ParentID,case when b.parentid =0 then a.parentid else isnull(b.parentid,0) end Topid From #MyTable a 
    	Left outer join #MyTable b on a.ParentID=b.ID

    Thanks

    Monday, August 11, 2014 8:54 AM
  • Thanks its working.

    Just a word of caution that above method will work only if there's a maximum of upto 2 levels

    If you can have multiple levels you need to use something like this

    ;With CTE
    AS
    (
    SELECT ID,ParentID, CAST(ID AS varchar(max)) AS Path
    FROM Table
    WHERE parentID = 0
    UNION ALL
    SELECT t.ID,t.ParentID,CAST(Path + '\' + CAST(ID AS varchar(max)) AS varchar(max))
    FROM CTE c
    JOIN Table t
    ON t.ParentID = c.ID
    )
    UPDATE t
    SET t.TopID = LEFT(Path,CHARINDEX('\',Path + '\')-1)
    FROM Table t
    INNER JOIN CTE c
    ON c.ID = t.ID 


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, August 11, 2014 8:59 AM
  • Hi,

    UPDATE t
    SET t.TopID = LEFT(Path,CHARINDEX('\',Path + '\')-1)
    FROM Table t
    INNER JOIN CTE c
    ON c.ID = t.ID

    in above update query, i got a error 'Invalid Column name ToPID'. i dont have TOPID column in my Table. i need to populate dynamically.

    Thanks

    Monday, August 11, 2014 9:23 AM
  • Hi Thangam - Kasim

    OK, I get that you need the top ID :-) I see that you got 2 great solutions.

    Please make sure that you read  Visakh16 response, regarding the first solution.

    In any case, here is a bit faster solution.

    ;with MyCTE as (
    	select 
    		T1.ID, T1.Description,T1.ParentID, 0 as TopID
    	from #MyTable T1
    	where ParentID = 0
    
    	union all
    
    	select 
    		T1.ID,T1.Description,T1.ParentID, CASE when isnull(T2.TopID,0)=0 then T2.ID else T2.TopID end as TopID
    	from #MyTable T1
    	inner join MyCTE T2 on T1.ParentID = T2.ID
    )
    select * from MyCTE
    Order by ID asc
    I hope this is useful :-)


    [Personal Site] [Blog] [Facebook]signature

    Monday, August 11, 2014 12:19 PM
    Moderator