none
SQL Select

    Question

  • I have a table called bom10500 that looks like the first table below.  What kind of query can I create from this table that gives me a fifth column called "top level" as shown in the second table below?  I am not sure what kind of TSQL query that will give me this...

    Part Component Group Sub Unit
    878-00 878-01 A 1
    878-00 878-02 A 0
    878-00 878-03 A 0
    878-00 878-04 A 0
    878-00 878-05 B 1
    878-00 878-06 B 0
    878-00 878-07 C 1
    878-00 878-08 C 0
    878-00 878-09 C 0
    Part Component Group Sub Unit Top Level
    878-00 878-01 A 1 878-01
    878-00 878-02 A 0 878-01
    878-00 878-03 A 0 878-01
    878-00 878-04 A 0 878-01
    878-00 878-05 B 1 878-05
    878-00 878-06 B 0 878-05
    878-00 878-07 C 1 878-07
    878-00 878-08 C 0 878-07
    878-00 878-09 C 0 878-07


    http://www.isolutionspartners.com/


    • Edited by Ryan McBee Wednesday, June 18, 2014 11:23 PM title
    Wednesday, June 18, 2014 10:48 PM

Answers

  • declare @forumTable table (Part char(6), Component char(20), [Group] char(1), [Sub Unit] tinyint)
    insert into @forumTable (Part, Component, [Group], [Sub Unit])
    values
    ('878-00', 'BubbleWrap	', 'A', 1), 
    ('878-00', '101100		', 'A', 0), 
    ('878-00', '11005600	', 'A', 0), 
    ('878-00', 'ShrinkWrap	', 'A', 0), 
    ('878-00', '878500		', 'B', 1), 
    ('878-00', '878000		', 'B', 0), 
    ('878-00', 'BubbleWrap	', 'C', 1), 
    ('878-00', '101100		', 'C', 0), 
    ('878-00', '11005600	', 'C', 0)
    
    select f1.*, f2.Component as TopLevel 
      from @forumTable f1
        inner join @forumTable f2
          on f1.[group] = f2.[group]
    	  and f2.[sub unit] = 1
    

    This should do the trick.

    Its worth noting that using reserved words and names with illegal characters (like white space) will cause you frustrations.

    Thursday, June 19, 2014 3:25 PM

All replies

  • Untested, but something quite close to this should do it:  (It looks as if Sub_Unit isn't part of picking the Top_Level, right?).  Adapt to your table, columns, etc.

    Select *
       , Min(Component) over(partition by Part, Group Order by Component) as Top_Level
      from YOURTABLE
    Hint: for the highest quality answers and tested/mocked up answers, also add to your post a query that recreates the test data you provide.


    Wednesday, June 18, 2014 11:41 PM
  • I have a table called bom10500 that looks like the first table below.  What kind of query can I create from this table that gives me a fifth column called "top level" as shown in the second table below?  It will be the component value of the within the group column where the sub unit value =1.  For example, group A would return "BubbleWrap" and group B would return 878500 since the sub unit column of the group is a 1.  Any ideas on what kind of select query to use?

    Part Component Group Sub Unit
    878-00 BubbleWrap A 1
    878-00 101100 A 0
    878-00 11005600 A 0
    878-00 ShrinkWrap A 0
    878-00 878500 B 1
    878-00 878000 B 0
    878-00 BubbleWrap C 1
    878-00 101100 C 0
    878-00 11005600 C 0
    Part Component Group Sub   Unit Top Level
    878-00 BubbleWrap A 1 BubbleWrap
    878-00 101100 A 0 BubbleWrap
    878-00 11005600 A 0 BubbleWrap
    878-00 ShrinkWrap A 0 BubbleWrap
    878-00 878500 B 1 878500
    878-00 878000 B 0 878500
    878-00 BubbleWrap C 1 BubbleWrap
    878-00 101100 C 0 BubbleWrap
    878-00 11005600 C 0 BubbleWrap


    http://www.isolutionspartners.com/

    Thursday, June 19, 2014 3:17 PM
  • declare @forumTable table (Part char(6), Component char(20), [Group] char(1), [Sub Unit] tinyint)
    insert into @forumTable (Part, Component, [Group], [Sub Unit])
    values
    ('878-00', 'BubbleWrap	', 'A', 1), 
    ('878-00', '101100		', 'A', 0), 
    ('878-00', '11005600	', 'A', 0), 
    ('878-00', 'ShrinkWrap	', 'A', 0), 
    ('878-00', '878500		', 'B', 1), 
    ('878-00', '878000		', 'B', 0), 
    ('878-00', 'BubbleWrap	', 'C', 1), 
    ('878-00', '101100		', 'C', 0), 
    ('878-00', '11005600	', 'C', 0)
    
    select f1.*, f2.Component as TopLevel 
      from @forumTable f1
        inner join @forumTable f2
          on f1.[group] = f2.[group]
    	  and f2.[sub unit] = 1
    

    This should do the trick.

    Its worth noting that using reserved words and names with illegal characters (like white space) will cause you frustrations.

    Thursday, June 19, 2014 3:25 PM
  • Select t.Part, t.Component, t.Group, t.SubUnit, t1.Component As TopLevel
    From <your table name> t
    Inner Join <your table name> t1 On t.Part = t1.Part And t.Group = t1.Group And t1.SubUnit = 1
    Order By Part, Group;

    Tom
    Thursday, June 19, 2014 3:26 PM
  • Make sure to always provide the DDL to simply create a repro, this will make it much easier for people to help you. To extend this to a hierarchical query, you could also use a recursive CTE for this. Although the following example does not extend that much to recursion as it could do:

    DECLARE @TestTable TABLE
    (
    	Part NVARCHAR(MAX),
    	Component NVARCHAR(MAX),
    	GroupName NVARCHAR(MAX),
    	Sub_Unit NVARCHAR(MAX)
    )
    
    INSERT INTO @TestTable
    VALUES
    	('878-00','BubbleWrap','A','1'), 
    	('878-00','101100','A','0'), 
    	('878-00','11005600','A','0'), 
    	('878-00','ShrinkWrap','A','0'),
    	('878-00','878500','B','1'), 
    	('878-00','878000','B','0'),
    	('878-00','BubbleWrap','C','1'), 
    	('878-00','101100','C','0'),
    	('878-00','11005600','C','0') 
    
    
    ;WITH CTE(Part,Component,GroupName,Sub_Unit, TopLevel)
    AS
    (
    	SELECT Part,Component,GroupName,Sub_Unit, Component AS TopLevel 
    	FROM @TestTable 
    	WHERE Sub_Unit = 1
    	UNION ALL 
    	SELECT 
    		T.Part,T.Component,T.GroupName,T.Sub_Unit, C.Component AS TopLevel
    	FROM @TestTable T
    	INNER JOIN CTE C
    	ON T.GroupName = C.GroupName
    	AND C.Sub_Unit = 1 AND T.Sub_Unit != 1
    )
    SELECT * FROM CTE  
    

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Thursday, June 19, 2014 3:38 PM
  • I thought about offering a recursive solution, but decided against it as this is just a single level.
    Thursday, June 19, 2014 6:01 PM
  • Hi,

    please make sure that you mark the posts as answers that helped you answering the question.

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Thursday, June 19, 2014 8:24 PM
  • And @the OP, don't just start new threads on the same basic topic.  If you realize you need to clarify the explanation, then clarify the explanation, it causes others extra time and effort when people make multiple posts.
    Thursday, June 19, 2014 8:47 PM