Combining Rows

Posted on 2011-05-10
Last Modified: 2012-06-27
I have a query which, through joins and such, produces sort of the correct results.  The problem is that it produces two rows.

The results are something list this
Customer  Sell_Date  Parts  Labour  Pay_Date
12345678  05-30-11   12.34  50.00
12345678             24.56  12.50   06-02-11
98765432  05-22-11   18.23  12.50
98765432             16.19  32.00   05-26-11

Open in new window

I want to merge th rows so that I have an output like this:
Customer  Sell_Date  Parts  Labour  Pay_Date
12345678  05-30-11   36.90  62.50   06-02-11
98765432  05-22-11   34.42  44.50   05-26-11

Open in new window

I know how to do it with the numbers, but how do I get the dates to merge?
Question by:Clif
    LVL 142

    Accepted Solution

    something like this could do:

    select Customer, Min(Sell_Date), sum(Parts), sum(Labor), may(pay_date) from yourtable group by Customer

    Open in new window

    LVL 16

    Expert Comment


    Try the Below Query.. so use this as an Outer query to get the desired results..Replace the Table Name with your query like

    Select .... FROM ()X
    Group By ...

    SELECT Customer, MAX(Sell_date) as Sell_Date, SUM(Parts) as Parts , SUM(Labour) as Labour, Max(Pay_Date) as Pay_Date
    FROM TableName
    GROUP BY Customer
    LVL 11

    Author Closing Comment

    Perfect, thanks.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    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.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    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.

    731 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

    18 Experts available now in Live!

    Get 1:1 Help Now