Combining records in a data grid

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:

PartNumber             Quantity
163845483                    5
163845483                    4
163845483                    3

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 <> 
imMTownStock.Quantity)
Mike MillerSoftware EngineerAsked:
Who is Participating?
 
ShauliConnect With a Mentor Commented:
If you want to sum Main.Quantity based on part_number then try that, but check how it affects the other fields, in terms of not missing other required data.

S
0
 
kulifajCommented:
If there isn't too much records shown at a one time, you can loop through the recordset and create a new recordset with in required format.
0
 
ShauliCommented:
You can use the sun distinct:

SUM(DISTINCT  [Main].Quantity) AS CountedQuantity

SELECT     TOP 100 PERCENT [Main].PART_NUMBER as PartNumber, SUM(DISTINCT [Main].Quantity) AS CountedQuantity FROM ... WHERE ... ORDER BY ...

S

0
 
Mike MillerSoftware EngineerAuthor Commented:
Like this???


SELECT TOP 100 PERCENT [Main].PART_NUMBER as PartNumber, SUM(DISTINCT [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 <> 
imMTownStock.Quantity)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.