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

Posted on 2014-08-07
Last Modified: 2014-08-24
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]
Question by:hojohappy
    LVL 34

    Expert Comment

    by:Brian Crowe
    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.
    LVL 65

    Expert Comment

    by:Jim Horn
    Give us a data mockup, both before and after, of what you're trying to pull off here.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    LVL 47

    Accepted Solution

    "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
    LVL 25

    Expert Comment

    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
    LVL 31

    Expert Comment

    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

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now