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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

SQL Server Select Query usng Group By with ability to select all columns

I need a SQL Select Query that will not do duplicate matches against a second table.  I have two tables that are joined but table [Management Report Active List]. has duplicate Product Codes.   Group by statement would be perfect but I need to be able to show all columns from both tables so how can I use group by and still display all columns.,

SELECT *   (how can I list all columns and still use group by)
FROM [Management Report Active List]
INNER JOIN [Consolidated List] ON [Management Report Active List].[Product Code] = [Consolidated List].[Product_Code]
GROUP BY [Management Report Active List].[Product Code]
1 Solution
Brian CroweCommented:
What you're asking for doesn't make sense.  You can't group AND display everything at the same time.  The purpose of grouping is to aggregate.  If there are multiple matching entries in Consolidated List then you need to decide how you want to display the child rows.

Please provide sample output so we can understand better what you are trying to achieve.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give us a data mockup, both before and after, of what you're trying to pull off here.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

"so how can I use group by and still display all columns.,"?

Sounds to me like you want ROW_NUMBER() :- not group by

BUT; like everyone before me, it's impossible for us to know the best solution without more information;

sample data (per table!) and expected results please
you need to use an aggregate function on each of the fields of the consolidated list table.

if you don't care which row's information will be displayed, just use either min or max
You need to help the experts help you. Sample input and output has been requested and, if you provide it, I will bet you can get a solution.

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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