To write Sql Query

• su_kumar11

  SSCommitted

  Points: 1675

  More actions

  August 8, 2008 at 12:22 am

  #124648

  Hi,

  I would like to write a query for following structure table...

  Id Text Parentid

  1 xyz NULL

  2 pqr NULL

  3 abc 1

  4 mno 1

  5 xxy 3

  6 oop 3

  7 umn 2

  I want my result like..................

  Parentname child Rank

  xyz abc 1

  abc xyz 2

  Please help me to write for query...

  Regards,

  Sunil..

• Abhijit More

  SSCertifiable

  Points: 6897

  More actions

  August 8, 2008 at 12:42 am

  #854930

  hope this wud help you...

  CREATE TABLE #test(

  IDINT,

  TextCHAR(3),

  ParentIDINT

  )

  INSERT#test

  SELECT1, 'xyz', NULL UNION

  SELECT2, 'pqr', NULL UNION

  SELECT3, 'abc', 1 UNION

  SELECT4, 'mno', 1 UNION

  SELECT5, 'xyz', 3 UNION

  SELECT6, 'oop', 3 UNION

  SELECT7, 'umn', 2

  SELECT * FROM #test;

  SELECTB.Text AS ParentText, A.Text AS ChildText, ROW_NUMBER() OVER(ORDER BY B.Text DESC ) AS Rank

  FROM#test A

  LEFT JOIN #test B ON A.ParentID = B.ID

  WHEREA.Text IN ('abc', 'xyz') AND A.ParentID IS NOT NULL

  DROP TABLE #test

  Abhijit - http://abhijitmore.wordpress.com