How to do Recursive program to find Drilldown data

• sampathsoft

  SSC Eights!

  Points: 855

  More actions

  January 10, 2011 at 12:06 am

  #232997

  I have to find all breakdowns (Drilldown) in my number sequence . Numbers are like

  1

  1.1

  1.2

  2

  2.1

  2.1.1

  2.1.2

  2.2 ......

  When enter number one in my front end i want to find it breakdown(Drilldown) . Breakdowns are in my database table.

  I used while loop with temporary table. But its not a way to find all breakdown(Drilldown).

  Thanks

• tommyh

  SSCertifiable

  Points: 6302

  More actions

  January 10, 2011 at 1:24 am

  #1271268

  Not really enough information to give a good answer.

  Read http://www.sqlservercentral.com/articles/Best+Practices/61537/ on how better to post your question.

  /T

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  January 10, 2011 at 8:32 am

  #1271441

  IF you don't have many rows in the hierarchy and IF you don't want to take the time to rework the form of the hierarchical path (it won't sort correctly overall), then you could try something like this (which will be absolutely horrible for performance for large hierarchies) to get you out of the woods.

  --===== Create some sample data. This is NOT a part of the solution.

  CREATE TABLE #SomeHierarchy (HierarchyPath VARCHAR(50))

  INSERT INTO #SomeHierarchy (HierarchyPath)

  SELECT '1' UNION ALL

  SELECT '1.1' UNION ALL

  SELECT '1.2' UNION ALL

  SELECT '2' UNION ALL

  SELECT '2.1' UNION ALL

  SELECT '2.1.1' UNION ALL

  SELECT '2.1.2' UNION ALL

  SELECT '2.2' UNION ALL

  SELECT '10' UNION ALL

  SELECT '10.1' UNION ALL

  SELECT '10.1.1' UNION ALL

  SELECT '10.1.2' UNION ALL

  SELECT '10.2'

  --===== Just show what we have in the table.

  -- This is NOT a part of the solution

  SELECT * FROM #SomeHierarchy

  DECLARE @Search VARCHAR(50)

  SELECT @Search = '1'

  SELECT *

  FROM #SomeHierarchy

  WHERE LEFT(HierarchyPath +'.',LEN(@Search)+1) = @Search+'.'

  ORDER BY HierarchyPath

  Like I said, as clever as that may look, it's not. It will have terrible performance problems on larger hierarchies and, if you want 100% correct numerical sorting, you'll need to change the underlying data. Post back if you need to do that. Read the first link in my signature below before you post back so we can help you better.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)