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, 2012 at 09:38 AM in Default

avatar image

kalichetisuresh1
20 1 1 2

in your sample data you have

5 0 Parent_B Child_B1

is this correct?

Oct 16, 2012 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, 2012 at 10:43 AM

avatar image

Cyborg
10.8k 37 54 51

(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, 2012 at 10:44 AM

avatar image

mjharper
2k 3 7 13

(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, 2012 at 10:46 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(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.

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:

x2072

asked: Oct 16, 2012 at 09:38 AM

Seen: 520 times

Last Updated: Oct 16, 2012 at 11:48 AM

Copyright 2016 Redgate Software. Privacy Policy