Combine 2 or more rows having characters and others having currency

Question

I have an output like this by a query:

ID      customer_name_Now    customer_name_Before       MOVEMENT
123451  Rustle Bock ltd      N                          £2,121
123451  N                    Rustle Bock ltd           -£25,666,899
123452  Little Garage Ltd    N                          £6,987
123453  N                    The Big Shop               £15,850

Story is that, I have 2 months of data. In both months the customer may or may not have movements depending if it was our customer last month or if its a customer now. Many cases it is customer in both months, hence I get 2 rows like the above.

The ideal output should be :

ID      customer_name_Now   customer_name_Before      MOVEMENT
123451  Rustle Bock ltd     Rustle Bock ltd          -£25,664,778
123452  Little Garage Ltd   N                         £6,987
123453  N                   The Big Shop              £15,850

So movements should sum to give me the actual movement in months and the names of customer should be in both columns given the customer has relation in both months.

@DMK the query I used to get the initial output is:

select /*+ NO_REWRITE */
customer_id,
customer_name_now,
customer_name_before,
movement

from
    (select /*+ NO_REWRITE */
    main.customer_id,
    main.customer_name_now,
    main.customer_name_before,
    main.limits_before,
    main.limits_now,
    sum(main.limits_now-main.limits_before) as movement

    from
        (select /*+ NO_REWRITE */
        customer_id,
        (customer_name_before) as customer_name_before,
        (customer_name_now) as customer_name_now,
        sum(limits_current) as limits_now,
        sum(limits_previous) as limits_before

        from
             (select /*+ NO_REWRITE */
             sub.customer_id,
             sub.customer_name_now,
             sub.customer_name_before,
             sub.limits_current,
             sub.limits_previous
             from
                 (select /*+ NO_REWRITE */
                 T2.customer_ID,
                 (T2.customer_name) customer_name_now,
                 'N' customer_name_before,

                 sum(T26.AGREED_LIMIT) limits_current,
                 0 limits_previous
                 from 

                 DWH_customer_HISTORY T2,
                 DWH_TIME_DIM T25,
                 DWH_FACILITY_MONTHLY T2
                 where  
                 ---some internal filters are applied here, i habe ot shown coz of security reasons----
                 and 
                 T25.MONTH_END = '2012-11-30' and 

                 group by 
                 T2.customer_ID,
                 T2.customer_name,
                 ) sub

             union all

             select /*+ NO_REWRITE */
             sub.customer_id,
             sub.customer_name_now,
             sub.customer_name_before,
             sub.limits_current,
             sub.limits_previous
             from
                 (select /*+ NO_REWRITE */
                 T2.customer_ID,
                 'N' as customer_name_now,
                 (T2.customer_name)customer_name_before,

                 0 limits_current,
                 sum(T2.AGREED_LIMIT) limits_previous,

                from 
                DWH_customer_HISTORY T2,
                DWH_TIME_DIM T25,
                DWH_FACILITY_MONTHLY T2
                where  
                ---some internal filters are applied here, i habe ot shown coz of security reasons----
                and 
                T25.MONTH_END = '2012-10-31'
                group by 
                T2.customer_ID,
                T2.customer_name,) sub
            ) un
        group by
        customer_id,
        customer_name_now,
        customer_name_before,) main

    group by 
    main.customer_id,
    main.customer_name_now,
    main.customer_name_before,
    main.limits_before,
    main.limits_now)

Answer 1

I've assumed your using SqlServer, though the query below will work in MySql also.

Select c1.ID, c1.customer_name_Now, c2.customer_name_Before, Total
from Customers c1
left Join Customers c2 
on c2.ID = c1.ID
left join
    (select ID as ID2, sum(MOVEMENT) as Total, count(*) as Cnt
    from Customers 
    group by ID) t1
on ID2 = c1.ID
where (c1.customer_name_Now <> 'N' and c2.customer_name_Before <> 'N') 
or CNT = 1 

Have a look at the following demo if your unsure

SqlFiddle

---

After looking at the Query you just added the above should still work. You either need to

• Substitute my table Customers with your query
• Or move your results from your query to a temporary table and substitute my table Customers with the temporary table

I'd go for the second. Saves re-running the same query over again.

Answer 2

In SQLServer2005+ use OVER clause

SELECT DISTINCT 
  ID,MAX(customer_name_Now) OVER (PARTITION BY ID) AS customer_name_Now, 
  MAX(customer_name_Before) OVER (PARTITION BY ID) AS customer_name_Before,
  SUM(MOVEMENT) OVER (PARTITION BY ID) AS MOVEMENT 
FROM your_table

OR

SELECT ID, MAX(customer_name_Now) AS customer_name_Now,
       MAX(customer_name_Before) AS customer_name_Before,
       SUM(MOVEMENT) AS MOVEMENT
FROM your_table
GROUP BY ID

Demo on SQLFiddle