Post reply

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

  • 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,

  • 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

  • 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

  • October 18, 2016 at 6:37 pm

    #1907298

    I came back to say thank you again!!!

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply