I need to get some data into a DataGrid.
I am currenlty using the query posted below. Is there any way to combine records in my results of this query based on PartNumber. I.E. If a PartNumber occurs multiple times in a result set (the Quantity could be different or the same for all records) Can I combine all the records into one adding more columns for the different quantities. So the result would include:
PartNumber Quantity Quantity Quantity
163845483 5 4 3
as opposed to:
Here's the query...
SELECT [Main].PART_NUMBER as PartNumber, [Main].Quantity as CountedQuantity,
[Main].Location, [Main].Cost, [Main].Team, [Main].Tag, [imMTownStock].Quantity
as SMFQuantity, cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) -
cast(Main.Cost as float) * CAST(Main.Quantity as float) as NetDollar From [Main] LEFT JOIN
imMTownStock ON (Main.PART_NUMBER = imMTownStock.PartNumber AND [Main].Quantity <>