Need Help in Recursive Query in SQL using CTE

• ravikumar401 69525

  Newbie

  Points: 3

  More actions

  September 14, 2015 at 8:12 am

  #399034

  I need help in sql recursive query, for example purpose i m providing sample table with insert script

  CREATE TABLE Details(

  parentid varchar(10), DetailComponent varchar(10) , DetailLevel int)

  GO

  INSERT INTO Details

  SELECT '','7419-01',0 union all

  SELECT '7419-01','44342-00',1 union all

  SELECT '7419-01','45342-00',1 union all

  SELECT '7419-01','46342-00',1 union all

  SELECT '7419-01','47342-00',1 union all

  SELECT '7419-01','48342-00',1 union all

  SELECT '7419-01','49342-00',1 union all

  SELECT '7419-01','50342-00',1 union all

  SELECT '50342-00','51342-00',2 union all

  SELECT '7419-01','52342-00',1 union all

  SELECT '52342-00','54342-00',2 union all

  SELECT '54342-00','54442-00',3 union all

  SELECT '54342-00','54552-00',3 union all

  SELECT '54552-00','R34S-54',4 union all

  SELECT '54552-00','R123-54',4 union all

  SELECT '54552-00','R111-54',4 union all

  SELECT 'R111-54','R222-54',5 union all

  SELECT 'R222-54','52342-00',6 union all

  SELECT '7419-01','TEST34-00',1 union all

  SELECT 'TEST34-00','445334-00',2 union all

  SELECT '445334-00','52342-00',3 union all

  SELECT '7419-01','1111-00',1 union all

  SELECT '7419-01','1111-00',1 union all

  SELECT '1111-00','52342-00',2

  GO

  SELECT * FROM Details

  From the above table data i want a search query , for example if I search data with "52342-00" I want output to be below format using CTE.

  NULL,'7419-01',0

  '7419-01','52342-00',1

  '7419-01','52342-00',1

  '52342-00','54342-00',2

  '54342-00','54552-00',3

  '54552-00','R111-54',4

  'R111-54','R222-54',5

  'R222-54','52342-00',6

  thanks

• Jeff Moden

  SSC Guru

  Points: 1003875

  More actions

  September 14, 2015 at 9:26 am

  #1826415

  So to summarize, given any DetailComponent, you want to return both the "upline" and the "downline" of that component?

  How many rows will end up in this table and how often is this table updated or inserted into? I ask because I might have something for you that will make your day.

  --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)
  

• Jayanth_Kurup

  SSC-Insane

  Points: 22967

  More actions

  September 14, 2015 at 9:43 am

  #1826427

  why are the second and third rows in the desired result set duplicates?

  Jayanth Kurup[/url]

• Dean Mincic

  Mr or Mrs. 500

  Points: 550

  More actions

  September 23, 2015 at 12:02 am

  #1828657

  Hi,

  If I got it right, the requirement is to pick a node from hierarchy and go in both directions, reverse to the root and down to the leaf level.

  I set up an example that might help:

  CREATE TABLE #hierarchy (StaffNo int ,Name varchar(20) , ManagerNo int)

  GO

  INSERT INTO #hierarchy(StaffNo,Name,ManagerNo)

  SELECT 100,'Dave',NULL

  UNION ALL

  SELECT 101,'Robert',100

  UNION ALL

  SELECT 102,'Alex',100

  UNION ALL

  SELECT 107,'Quentin',100

  UNION ALL

  SELECT 103,'Ted',101

  UNION ALL

  SELECT 104, 'Jack',101

  UNION ALL

  SELECT 105,'Tony',103

  UNION ALL

  SELECT 106,'Boby',103

  UNION ALL

  SELECT 108,'Sam',107

  UNION ALL

  SELECT 109,'Nick',107

  UNION ALL

  SELECT 110,'Dean',108 -- start node

  UNION ALL

  SELECT 111,'Simon',108

  UNION ALL

  SELECT 112,'Renee',110

  UNION ALL

  SELECT 113,'Jason',110

  UNION ALL

  SELECT 114,'Mark',113

  UNION ALL

  SELECT 115,'Roger',113

  --SELECT * FROM #hierarchy

  DECLARE @StaffNo INT = 110;

  ;WITH ReverseHierarchy(StaffNo,Name,ManagerNo)

  AS

  (

  SELECT hi.StaffNo

  ,hi.Name

  ,hi.ManagerNo

  FROM #hierarchy hi

  WHERE hi.StaffNo = @StaffNo

  UNION ALL

  SELECT hi1.StaffNo

  ,hi1.Name

  ,hi1.ManagerNo

  FROM #hierarchy hi1

  INNER JOIN ReverseHierarchy rh

  ON rh.ManagerNo = hi1.StaffNo

  ),ForwardHierarchy(StaffNo,Name,ManagerNo)

  AS

  (

  SELECT hi.StaffNo

  ,hi.Name

  ,hi.ManagerNo

  FROM #hierarchy hi

  WHERE hi.StaffNo = @StaffNo

  UNION ALL

  SELECT hi2.StaffNo

  ,hi2.Name

  ,hi2.ManagerNo

  FROM #hierarchy hi2

  INNER JOIN ForwardHierarchy fh

  ON fh.StaffNo = hi2.ManagerNo

  )

  SELECT rh.StaffNo,rh.Name,rh.ManagerNo FROM ReverseHierarchy rh

  UNION

  SELECT fh.StaffNo, fh.Name, fh.ManagerNo FROM ForwardHierarchy fh

  /*

  StaffNo Name ManagerNo

  ----------- -------------------- -----------

  100 Dave NULL --Root

  107 Quentin 100

  108 Sam 107

  110 Dean 108 --Starting node

  112 Renee 110

  113 Jason 110

  114 Mark 113 --leaf

  115 Roger 113 --leaf

  */

  -- The multi CTE is just to prove the concept. In production I would probably use separate queries and avoid code duplication (the anchors)

  🙂

  D.Mincic
  😀
  MCTS Sql Server 2008, Database Development

• Dean Mincic

  Mr or Mrs. 500

  Points: 550

  More actions

  September 23, 2015 at 12:05 am

  #1828658

  -

  D.Mincic
  😀
  MCTS Sql Server 2008, Database Development

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  September 23, 2015 at 7:54 am

  #1828790

  Probably it's cycle detection problem

  The query below will return all cycles containing 52342-00

  with r as (

  select parentid , DetailComponent , DetailLevel

  , cast( '->' + DetailComponent + '->' as varchar(max)) AS hierarchypath

  from Details

  where parentid =''

  union all

  select d.parentid , d.DetailComponent , d.DetailLevel

  , hierarchypath + d.DetailComponent + '->'

  from r

  join Details d on d.parentid = r.DetailComponent

  and hierarchypath not like '%->' + d.parentid + '->' + d.DetailComponent + '->%'

  )

  select * from r

  -- cycling paths only and containing 52342-00

  where hierarchypath like '%'+ DetailComponent + '->%'+ DetailComponent + '%'

  and hierarchypath like '%->52342-00->%'

  Then one can choose paths and extract steps if i'm guessing the problem right. Building xml - like hierarchypath string may help reversing the string into rowset.

• Jeff Moden

  SSC Guru

  Points: 1003875

  More actions

  September 23, 2015 at 8:04 am

  #1828797

  Now, if we could just get the OP to participate in his own thread... 😉

  --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)