Please help me out in solving this.Really Challenging and brain teaser to sql masters

SELECT T.TID, T.ParentID, ISNULL(T2.Description, T.Description) as Parent, T.Description
FROM MyTable T
LEFT JOIN MyTable T2
ON (T.ParentID  = T2.TID)

can you try this? Not tested

I think there's a typo in your sample data - I think for TID = 4 you should have ParentID = 0 and then for TID = 5, 6 and 7 ParentID should be 4.

Assuming that's the case then LEFT JOINing the data to itself should give you what you need as the code below shows....

IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test
CREATE TABLE #test
 (
   TID INT
 , ParentID INT
 , Description VARCHAR(50)
 )
INSERT INTO #test
 ( TID, ParentID, Description )
VALUES ( 1, 0, 'Parent_A' ),
 ( 2, 1, 'Child_A1' ),
 ( 3, 1, 'Child_A2' ),
 ( 4, 0, 'Parent_B' ),
 ( 5, 5, 'Child_B1' ),
 ( 6, 5, 'Child_B2' ),
 ( 7, 5, 'Child_B3' )


SELECT a.TID
   , a.ParentID
   , b.Description
   , ISNULL(b.Description, a.Description) AS Parent
   , a.Description
FROM #test a
 LEFT JOIN #test AS b
 ON a.ParentID = b.TID
ORDER BY a.TID 

If the demo data is altered slightly then this works:

IF OBJECT_ID('tempdb..#Demo') IS NOT NULL 
 DROP TABLE #Demo
GO


CREATE TABLE #Demo
 (
   TID INT ,
   PID INT ,
   [Description] VARCHAR(20)-- column_name data_type,...
 )

INSERT [#Demo]
 ( [TID], [PID], [Description] )
 VALUES ( 1, 0, 'Parent_A' ),
 ( 2, 1, 'Child_A1' ),
 ( 3, 1, 'Child_A2' ),
 ( 4, 0, 'Parent_B' ),
 ( 5, 4, 'Child_B1' ),
 ( 6, 4, 'Child_B2' ),
 ( 7, 4, 'Child_B3' )


SELECT [d].[TID] ,
 [d].[PID] ,
 CASE WHEN [d].[PID] = [d].[TID] THEN d2.[Description]
 ELSE [d].[description]
 END AS Parent ,
 [d].[Description]
    FROM [#Demo] AS d
 LEFT JOIN [#Demo] AS d2 ON [d].[PID] = [d2].[TID]
WHERE [d].[TID] IS NOT NULL