roll up

sam2929
sam2929 used Ask the Experts™
on
Hi,
I have
Source:
Rq_id    event_id   event_code       cust number
1001        create          create              1005
1001         out              
1001         scucess       pickup             2001

So we want to roll up what we want is
Target will have just one row
Rq_id    event_id   event_code       cust number
1001       create         pickup               2001

So basic idea is pickup a row with event_id create and event_code pickup and cust number 2001 and then rollup it to one line only
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SharathData Engineer

Commented:
I don't understand fully. Do you want this?

select * from your_table where cust_number = 2001 and event_code = 'pickup'
Hi Sam,

It looks like you really want to pick up (parts of) two rows.  In one row the event_id is 'create' and in the other row the event_code is 'pickup'.  I'm assuming that there are other fields that you'll want to access?

A self join of the table will put the two rows on 1 line.  You can grab what you need.  The query below demonstrates it.  By using an OUTER join, all 'create' records are selected even if the pickup isn't complete.  If you want only the rows with both create and pickup, change the join type to INNER.


Good Luck,
Kent

SELECT *
FROM mytable t0
LEFT JOIN mytable t1
  ON t0.rq_id = t1.rq_id
 AND t0.event_id = 'create'
 AND t1.event_code = 'pickup'

Open in new window

Author

Commented:
Thanks
what if i want sumthing like this
Source:
Rq_id    event_id   event_code       cust number        flag
1001        create          create              1005  
1001         out              
1001         sucess       pickup             2001      
1001         dump                                                                 Y

Result
Rq_id    event_id   event_code       cust number    flag
1001       create         pickup               2001            Y


Yep.  Just pick the correct fields (columns) from the query above.  Not sure how you're generating "flag".


Kent

SELECT t0.rq_id, t0.event_id, t1.event_code, t0.cust_number
FROM mytable t0
LEFT JOIN mytable t1
  ON t0.rq_id = t1.rq_id
 AND t0.event_id = 'create'
 AND t1.event_code = 'pickup'

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial