sql server - how do i get hierarchy of data from two different tables?

• imanalimi

  SSC Journeyman

  Points: 87

  More actions

  October 17, 2016 at 11:19 pm

  #331367

  Hi Guys,

  I have two tables structured like this:

  tblStructure

  =================

  [VCode].....[Name]

  1...............WBS1

  2...............WBS2

  3...............WBS3

  tblItems

  =================

  [VCode]..........[Name]..........[StructureVCode]

  1.....................Item1............1

  2.....................Item2............1

  3.....................Item3............2

  4.....................Item4............2

  5.....................Item5............2

  Can anybody help me producing this table, using T-SQL Please?:

  [VCode]..........[Name]

  S1...................WBS1

  I1........................| Item1

  I2........................| Item2

  S2...................WBS2

  I3........................| Item3

  I4........................| Item4

  I5........................| Item5

  S3...................WBS3

  Thanks,

• Thom A

  SSC Guru

  Points: 99137

  More actions

  October 18, 2016 at 3:27 am

  #1907070

  When posting, it's always best to supply your DDL and INSERT statements (see my link to Jeff's post in my signature).

  Hopefully, however, this meets your goals:

  CREATE TABLE #Structure (VCode INT IDENTITY(1,1),

  [Name] VARCHAR(4));

  CREATE TABLE #Item (VCode INT IDENTITY (1,1),

  [Name] VARCHAR(8),

  StructureVCode INT);

  INSERT INTO #Structure ([Name])

  VALUES ('WBS1'),

  ('WBS2'),

  ('WBS3');

  INSERT INTO #Item ([Name], [StructureVCode])

  VALUES ('Item1', 1),

  ('Item2', 1),

  ('Item3', 2),

  ('Item4', 2),

  ('Item5', 2);

  SELECT T.Prefix + CASE T.Prefix

  WHEN 'S' THEN CAST(T.StructureVCode AS VARCHAR(4))

  WHEN 'I' THEN CAST(T.ItemVCode AS VARCHAR(4))

  END AS VCode,

  T.[Name]

  FROM (

  SELECT 'S' as Prefix,

  0 AS ItemVCode,

  S. VCode AS StructureVCode,

  S.[Name]

  FROM #Structure S

  UNION

  SELECT 'I' AS Prefix,

  I.VCode AS ItemVCode,

  S.VCode AS StructureVCode,

  I.[Name]

  FROM #Structure S

  JOIN #Item I ON S.VCode = I.StructureVCode

  ) T

  ORDER BY T.StructureVCode,

  T.ItemVCode;

  DROP TABLE #Structure;

  DROP TABLE #Item;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• imanalimi

  SSC Journeyman

  Points: 87

  More actions

  October 18, 2016 at 4:56 pm

  #1907292

  Thank you very much Thom,

  That was great. You solved my week problem!

  It was very smart. Thanks again

• imanalimi

  SSC Journeyman

  Points: 87

  More actions

  October 18, 2016 at 6:37 pm

  #1907298

  I came back to say thank you again!!!

• Jeff Moden

  SSC Guru

  Points: 1004592

  More actions

  October 18, 2016 at 6:58 pm

  #1907300

  @imanalimi ,

  Since this appears to be for a WBS structure, will you have additional levels than the two that you currently have?

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