none
Query BOM

    Question

  • I need some directio for querying a BOM by project number

    The following is a table of records of whet I need.

    Project number      Division    

    1                           HVD

    3                           PNC

    3                           FCG

    1                            AUT

    1                           LWN

    Resulst of query for project 1:

         HVD,AUT,LWN

    Thanks for the help

       

    Monday, August 05, 2013 7:36 PM

Answers

  • Hi sturner333,

    We can refer to the following codes:

     declare @tempTb table
     (
    	[Project number] int,
    	Division     char(3)
     )
     
     insert into @tempTb values(1,'HVD'),(3,'PNC'),(3,'FCG'),(1,'AUT'),(1,'LWN');
    
     select [Project number], STUFF(
    	(select ','+Division
    	from @tempTb t2
    	where t2.[Project number]=t1.[Project number]
    	for xml path('')
    	),1,1,''
     )
     from @tempTb t1
     group by [Project number]
    If you have any feedback on our support, please click here.


    Allen Li
    TechNet Community Support

    Wednesday, August 07, 2013 2:52 AM
    Moderator

All replies

    • Proposed as answer by Chris Sijtsma Monday, August 05, 2013 7:48 PM
    Monday, August 05, 2013 7:45 PM
    Moderator
  • Hi sturner333,

    We can refer to the following codes:

     declare @tempTb table
     (
    	[Project number] int,
    	Division     char(3)
     )
     
     insert into @tempTb values(1,'HVD'),(3,'PNC'),(3,'FCG'),(1,'AUT'),(1,'LWN');
    
     select [Project number], STUFF(
    	(select ','+Division
    	from @tempTb t2
    	where t2.[Project number]=t1.[Project number]
    	for xml path('')
    	),1,1,''
     )
     from @tempTb t1
     group by [Project number]
    If you have any feedback on our support, please click here.


    Allen Li
    TechNet Community Support

    Wednesday, August 07, 2013 2:52 AM
    Moderator
  • >> need some direction for querying a BOM by project number <<

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    You do not know that rows are not records! Then you say you want to avoid First Normal form (1Nf). Look at who is telling you that nothing you are posting is polite or correct. 

    I have chapters in my TREES & HIERARCHIES book on the BOM problem. Read it. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, August 07, 2013 3:45 AM
  • I know it is not polite to be rude just to hawk your book. EM
    Wednesday, August 07, 2013 11:48 AM