x

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

i have a table with 3 columns.


TID         ParentID           Description  
1           0             Parent_A
2           1             Child_A1
3           1             Child_A2
4           1             Parent_B
5           0             Child_B1                        
6           5             Child_B2
7           5             Child_B3

and my table column TID is auto Incremented(1,1) ..now i need to display the out put like


TID         ParentID     Parent         Description
1           0            Parent_A       Parent_A
2           1            Parent_A       Child_A1
3           1            Parent_A       Child_A2
4           1            Parent_B       Parent_B
5           0            Parent_B       Child_B1                        
6           5            Parent_B       Child_B2
7           5            Parent_B       Child_B3

now what my requirement is i want to show the Description column 2 times .one time as Parent and another time as Description .... and my task is if PID=0 means He is parent..if PID!=o then it is child i want to dispaly in parent column........
more ▼

asked Oct 16 '12 at 09:38 AM in Default

kalichetisuresh1 gravatar image

kalichetisuresh1
20 1 2

in your sample data you have

5 0 Parent_B Child_B1

is this correct?
Oct 16 '12 at 10:37 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Oct 16 '12 at 10:43 AM

Cyborg gravatar image

Cyborg
10.6k 36 39 45

(comments are locked)
10|1200 characters needed characters left

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 
more ▼

answered Oct 16 '12 at 10:44 AM

mjharper gravatar image

mjharper
1.3k 2 4 7

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 16 '12 at 10:46 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 73 77 107

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816

asked: Oct 16 '12 at 09:38 AM

Seen: 393 times

Last Updated: Oct 16 '12 at 11:48 AM