[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 148
  • Last Modified:

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)
0
Mike Miller
Asked:
Mike Miller
  • 2
1 Solution
 
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
 
ShauliCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now