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.You should do this on client part, not on server.|||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.|||how do i start the function, unable to get any idea.

should i use a cursor or what?|||Suppose you have ProductID, ProductName, and PartNumber fields in your table (ProductsParts). For every PartNumber the values of ProductID and ProductName are repeated, right?


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|||Thank You very much. It worked out very well.
Thanks Again.