asked on 5/16/2011

roll up

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

DB2

Last Comment

Kent Olsen

5/17/2011

Sharath S

5/16/2011

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

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

ASKER CERTIFIED SOLUTION

Kent Olsen

5/16/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

sam2929

5/17/2011

ASKER

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

Kent Olsen

5/17/2011

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