Hierarchical Dataset using a Self-Referencing Table

• JimiJett

  SSChasing Mays

  Points: 647

  More actions

  September 15, 2011 at 11:20 am

  #243855

  Hi All,

  I have a question that I've seen some various posts on but none quite getting what I want.

  SQL2005 is version.

  I have a self referencing table via an ID and ParentID relationship and want to return data in a hierarchical way to use in a tree view.

  I want to use either CTE or RANK and PARTITION functions to accomplish.

  I know I could do this in a loop and have seen this KB article:

  http://msdn.microsoft.com/en-us/library/aa172799(SQL.80).aspx

  However, I want to do with one select or CTE if possible.

  Here's the problem definition:

  I have a table of TradingPartners with a TradingPartnerID and a ParentTradingPartnerID, with other data like Name, etc in. The IDs may or may not be sequential. ie. Parent1 = 1 and Parent1Child1 = 2 and Parent1Child3 = 3 vs Parent2 = 55 and Parent2Child1 = 6, Parent2Child2 = 22, Parent2Child3 = 99, Parent2Child4 = 33, and so forth.

  I want to display the data in Parent/Child order like this (either by Name or ID for both):

  Some ID's have parents some don't.

  Parent1

  Parent1Child1

  Parent1Child2

  Parent1Child3

  Parent2

  Parent2Child1

  Parent2Child2

  Parent3

  Parent3Child1

  Parent3Child2

  Parent3Child3

  Parent4

  Parent5

  Parent6

  Parent6Child1

  Parent6Child2

  and so on.

  Here's some sample data:

  CREATE TABLE #tmp_TradingPartner ( TradingPartnerID INT, ParentTradingPartnerID INT, TradingPartnerName VARCHAR(50) )

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (555,0,'Andrew Corporation Cheshire')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (706,0,'Ferro - Technology Center')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (816,0,'Charming Shoppes - Outlets')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (546,555,'Andrew Corporation Joliet')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (547,555,'Andrew Corporation Columbus')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (548,555,'Andrew Corporation Euless')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (549,555,'Andrew Corporation Forest')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (550,555,'Andrew Corporation Garner')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (551,555,'Andrew Corporation Reynosa')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (552,555,'Andrew Corporation Richardson')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (553,555,'Andrew Corporation Warren')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (554,555,'Andrew Corporation Norcross')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (556,555,'Andrew Corporation McAllen')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (707,706,'Ferro - C&GPM Toccoa')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (708,706,'Ferro - C&GPM Orrville')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (714,706,'Ferro - LCD Plymouth')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (715,706,'Ferro - PAD Cleveland, Wayside')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (716,706,'Ferro - PAD Fort Worth')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (717,706,'Ferro - LCD Edison')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (718,706,'Ferro - EMS Penn Yan')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (719,706,'Ferro - Distribution Warehouse, LA')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (721,706,'Ferro - PAD Bridgeport')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (722,706,'Ferro - PFC Waukegan')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (723,706,'Ferro - Glass and Color')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (725,706,'Ferro - NAC 4150')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (726,706,'Ferro - EMS South Plainfield')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (727,706,'Ferro - PAD Walton Hills')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (728,706,'Ferro - APC Carpentersville')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (729,706,'Ferro - FRP Evansville')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (730,706,'Ferro - PCD Stryker')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (731,706,'Ferro - PCD South Plainfield')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (732,706,'Ferro - EMS 4150')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (733,706,'Ferro - EMS Vista')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (988,706,'Ferro - PFC Baton Rouge')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (989,706,'Ferro - C&GPM 4150')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (814,816,'Charming Shoppes - Fashion Bug')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (815,816,'Charming Shoppes. - Catherines')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (817,816,'Charming Shoppes - Lane Bryant')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (22,0,'Parent One No Child')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (33,0,'Parent Two No Child')

  INSERT INTO #tmp_TradingPartner (TradingPartnerID,ParentTradingPartnerID,TradingPartnerName) VALUES (44,0,'Parent Three No Child')

  Below are the two methods and how they are presenting data.

  Rank Version:

  SELECT

  tpp.TradingPartnerID, tpp.ParentTradingPartnerID, tpp.TradingPartnerName, RANK() OVER (PARTITION BY ParentTradingPartnerID ORDER BY TradingPartnerID) as Rank

  FROM

  #tmp_TradingPartner tpp WITH (NOLOCK)

  Output:

  Parent1

  Parent2

  Parent3

  Parent1Children

  Parent2Children

  Parent3Children

  CTE Version:

  WITH

  TradingPartnerHierarchy ( TradingPartnerID, TradingPartnerName, ParentTradingPartnerID, LevelID )

  AS

  (

  SELECT

  tp.TradingPartnerID,

  tp.TradingPartnerName,

  tp.ParentTradingPartnerId,

  0 AS LevelID

  FROM

  #tmp_TradingPartner tp WITH (NOLOCK)

  WHERE

  tp.ParentTradingPartnerID = 0

  AND

  tp.TradingPartnerID IN (555,706,816)

  UNION ALL

  SELECT

  tph.TradingPartnerID,

  tph.TradingPartnerName,

  tph.ParentTradingPartnerId,

  LevelID + 1

  FROM

  #tmp_TradingPartner tph WITH (NOLOCK)

  INNER JOIN

  TradingPartnerHierarchy tpph

  ON

  tph.ParentTradingPartnerID = tpph.TradingPartnerID

  )

  SELECT * FROM TradingPartnerHierarchy

  Output:

  Parent1

  Parent2

  Parent3

  Parent3Children

  Parent2Children

  Parent1Children

  Currently the specs want it ordered by Children within Parent either by name or by ID.

  Can someone please help with the ordering of the data (first) and second, which is better? The table in question currently has about 25K records with expected growth up to 150-250K records or so. ie. Small/Medium size table not expected to grow beyond a medium size.

  Thanks in advance.

  Howard

• Jeff Moden

  SSC Guru

  Points: 1004403

  More actions

  September 18, 2011 at 1:35 pm

  #1383124

  Please see the following article...

  http://www.sqlservercentral.com/articles/T-SQL/72503/

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

• JimiJett

  SSChasing Mays

  Points: 647

  More actions

  September 19, 2011 at 7:35 am

  #1383300

  Thanks Jeff, I had not seen that article. Very well written and great explanation.

• Jeff Moden

  SSC Guru

  Points: 1004403

  More actions

  September 20, 2011 at 6:11 pm

  #1384270

  Thank you for the great feedback, Howard.

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

• deepikamm

  SSCommitted

  Points: 1681

  More actions

  October 6, 2011 at 1:32 am

  #1391087

  Great Article..