Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that has columns like following. Batch number is a database design thing like row number

demand_id        batch_number    debit status     customer
     34               1             yes
     34               2                             jack 
     35               1             no                
     35               2                             kate 

I want to create a query that would return this:

   demand_id         debit status      customer
     34                  yes             jake
     35                  no              kate 

How can I do that?

share|improve this question
add comment

2 Answers

Group by the demand_id and aggregate the debit_status / customer.

SELECT Demand_Id
   , Max(Debit_Status) Debit_Status
   , Max(Customer) Customer
FROM yourtable GROUP BY Demand_Id;

This may not work if your sample is not representative.

SQL fiddle (data from Phil's).

share|improve this answer
add comment

Join the table to itself.

So:

select yt1.demand_id, yt1.debit_status, yt2.customer
from yourtable yt1, yourtable yt2
where yt1.demand_id = yt2.demand_id
and yt1.debit_status is not null
and yt2.customer is not null;

Or (if batch_number can reliably be used to fetch the 2 rows needed to make a single row:

select yt1.demand_id, yt1.debit_status, yt2.customer
from yourtable yt1, yourtable yt2
where yt1.demand_id = yt2.demand_id
and yt1.batch_number = 1
and yt2.batch_number = 2;

SQL fiddle.

share|improve this answer
    
Thanks... it was so trivial, i feel like stupid how did not i think that :) –  elimerv Feb 21 '13 at 15:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.