Query help Regarding Hierarchy

• Smash125

  Hall of Fame

  Points: 3562

  More actions

  October 4, 2012 at 7:59 am

  #263405

  My input Looks like this

  CREATE TABLE #DataToBeReOrdered

  (

  EmployeeID INT,

  Name NVARCHAR(100),

  ManagerID INT

  )

  INSERT INTO #DataToBeReOrdered(EmployeeID,Name,ManagerID)

  SELECT '1','Nik1','2'

  UNION ALL

  SELECT '2','Nik2',NULL

  UNION ALL

  SELECT '3','Nik3','2'

  UNION ALL

  SELECT '4','Nik4','3'

  UNION ALL

  SELECT '5','Nik5','1'

  UNION ALL

  SELECT '6','Nik6','3'

  UNION ALL

  SELECT '7','Nik7','1'

  UNION ALL

  SELECT '8','Nik8','5'

  UNION ALL

  SELECT '9','Nik9','1'

  I want the out put to look like this

  Employee Manager,Level

  Nik2SuperBoss1

  Nik1Nik22

  Nik3Nik22

  Nik4Nik33

  Nik6Nik33

  Nik5Nik13

  Nik7Nik13

  Nik9Nik13

  Nik8Nik54

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  October 4, 2012 at 8:08 am

  #1545561

  WITH Recur(EmployeeID,Employee,ManagerID,Manager,Level) AS (

  SELECT EmployeeID,Name,ManagerID,CAST('SuperBoss' AS NVARCHAR(100)) ,1

  FROM #DataToBeReOrdered

  WHERE ManagerID IS NULL

  UNION ALL

  SELECT d.EmployeeID,d.Name,d.ManagerID,r.Employee, r.Level+1

  FROM #DataToBeReOrdered d

  INNER JOIN Recur r ON r.EmployeeID = d.ManagerID)

  SELECT Employee,Manager,Level

  FROM Recur

  ORDER BY Level,Employee;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  October 4, 2012 at 12:58 pm

  #1545743

  Smash125 (10/4/2012)

  ---

  I want the out put to look like this

  Employee Manager,Level

  Nik2SuperBoss1

  Nik1Nik22

  Nik3Nik22

  Nik4Nik33

  Nik6Nik33

  Nik5Nik13

  Nik7Nik13

  Nik9Nik13

  Nik8Nik54

  Why? Depending on what you're trying to do, that doesn't actually depict the basic form of the hierarchy and is mostly useless for tree representations in pull-downs or any form of easy analysis or aggregation.

  What are you actually trying to do overall with this hierarchy? I know we can help. Of course, if you're happy with that output, then Mark's code does it nicely.

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