Display Hierarchy in SQL2008

• rhd110

  Ten Centuries

  Points: 1062

  More actions

  February 23, 2010 at 9:48 am

  #220876

  Please show how to display hierarchy in following situations:

  A little domain knowledge before starting

  An assembly can have multiple items and multiple subAssemblies.

  1. Table] datBillofMaterials

  Columns

  a. BillSequenceID

  b. AssemblyID

  2. Tables] datInventoryComponents

  Columns

  a. InventoryItemID

  b. BillSequenceID

  c. ComponentItemID

  d. Quantity

  e.g

  (1)Assembly A1

  (50)|___________ SubAssembly SA1

  ||______ I1(100)

  ||______ I2(101)

  |

  (51)|___________ Item I10(110)

  |

  (52)|___________ Item I20(111)

  |

  (53)|___________ SubAssembly SA2(150)

  |___________ Item I30(115)

  I have two table 1.]datAssemblies table and 2.]datComponents table

  1. datAssemblies table having all assemblies entry and Components will have all the mapping with subassemblies and items

  2. subAssemblies entries are also listed in datAssemblies also

  1.]datAssemblies (PK : BillSequenceID )

  BillSequenceID AssemblyID

  1 1

  2 50

  3 53

  4 150

  2.]datComponents (PK : InventoryItemID)

  InventoryItemID BillSequenceID ComponentItemID Qty

  1150 1

  2151 10

  3152 10

  4153 1

  52100 5

  62101 5

  73150 1

  8150115 6

  From the above we can see that the subassembly SA1 is used in main assembly A1 and its quantity is 1

  similarly SA2 is used in main assembly A2 and its quantity is 1

  These entries are in datAssemblies as well as seen

  Now I want to have two result set :

  1. The hierarchy of the assembly (tree structure)

  2. Items used in the assembly with quantity

  Please advice. I am in no position to use SQL2008 hierarchy datatype.

  Thanks in advance.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  February 23, 2010 at 10:29 am

  #1123756

  Please have a look at BOL (BooksOnLine, the help system installed with SQL Server). Especially look for "Manufacturing Scenarios, example A: Viewing a multilevel bill-of-materials list for a parent product"

  It sounds like the identical subject.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• GSquared

  SSC Guru

  Points: 260824

  More actions

  February 23, 2010 at 10:29 am

  #1123757

  Check this article, see if it helps: http://www.sqlservercentral.com/articles/T-SQL/65540/

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon