Matching duplicate records

[jhall01]

I have a query that pulls back 4 records per order ID When a service is changed the old service goes out and the new service goes in. I need to match the in/out services per order.

Example:

OrderID name serviceID_out service serviceID_in service
1 Jon abc service1
1 Jon xyz service2
1 Jon abc oldservice1
1 Jon xyz oldservice2


The query will always create blanks on the out when there is an in, and blanks on the in when there is an out. I need to get this query down to 2 records with the serviceID's Matching. There can be anywhere from 1 to 10 services going in and out on one order.

I want the above example to look like:

OrderID name serviceID_out service serviceID_in service
1 Jon abc oldservice1 abc service1
1 Jon xyz oldservice2 xyz service2

any Ideas on how to Group and match without creating tables and joining? I am pushing my limit on allocated space.

Thanks in advance!!!

Jon

 

[rrrkrishnan]

Try this.
select orderid,name,service,max(serviceid_In),max(serviceid_Out) from tbl
group by orderid,name,service

 

[jhall01]

That won't work because you still have to get the description of the service out. There are two service description columns. plus i need to maintain both serviceID's for that OrderID. taking the max will eliminate 1 of the products.

I may have made the mistake to call serviceID a service ID it is more of a service type.

abc is a type of service and the description of the service follow which contains the service code.

 

[dnoeth]

There's only one row for a given serviceID_in/serviceID_out?

select
orderid,
name,
COALESCE(serviceID_out, serviceID_in) as serviceID,
max(first_service_col),
max(second_service_col)
from tab
group by 1,2,3


Dieter