Results 1 to 6 of 6

Thread: Query

  1. #1
    Join Date
    Jun 2004
    Posts
    39

    Unanswered: Query

    Hi,
    I have a table with some product information. These products have related records in one more table with the manufacturing partno.
    Each product has multiple manufacturing part no.
    I want the product information and the manufacturing part no information
    in the same line. If the manufacturing part no is one, then beside all the product info, the query should add one column and display the manufacturing no. And if the product has 2 manufacturing part no, then it should add 2 columns each having the manufacturing part no respectively.
    I mean to say that there should be only one record for each product and beside the product info, it should have one/multiple fields showing the manufacturing model no.
    Can anyone help me out how to do this query.
    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    You should do this on client part, not on server.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Adding fields on the fly is not a good idea. What if there is an error in data entry or else where in the process of assigning manufacturer's part numbers to a product? Imagine the erroneous assignment resulted in 1500 part numbers associated with the same product, instead of 750 different ones? A better way would be to select distinct products, and in one (and only one) additional field have a comma-separated list of all associated manufacturer's part numbers, regardless of their number (well, preferrably where the numbers and the commas would fit into 8000-character field). For this to work all you need is a multi-statement function that returns varchar(8000) and takes product identifier as a parameter, so that it can be used in the same SELECT where you're retrieving distinct products.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2004
    Posts
    39
    how do i start the function, unable to get any idea.

    should i use a cursor or what?

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Suppose you have ProductID, ProductName, and PartNumber fields in your table (ProductsParts). For every PartNumber the values of ProductID and ProductName are repeated, right?

    Code:
    create function dbo.fn_PartNumbers (
    @ProductID int) returns varchar(8000) as
    begin
    declare @PartNumbers varchar(8000)
    set @PartNumbers = ''
    select @PartNumbers = @PartNumbers + PartNumber + ', '
    	 from ProductsParts where ProductID = @ProductID
    return (substring(@PartNumbers, 1, datalength(rtrim(@PartNumbers))-1))
    end
    And then, here's your query:

    select distinct ProductID, ProductName, PartNumbers = dbo.fn_PartNumbers(ProductID)
    from ProductsParts
    Last edited by rdjabarov; 07-26-04 at 17:09. Reason: misplaced parenthesis
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jun 2004
    Posts
    39
    Thank You very much. It worked out very well.
    Thanks Again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •