how to write this unpivot query [message #642487] |
Fri, 11 September 2015 14:48 |
|
zeninblue
Messages: 2 Registered: September 2015
|
Junior Member |
|
|
Hi,
I am trying to write a query to implement this:
source table is like:
order_id status order_date pack_date ship_date receive_date return_date order_quantity ship_quantity pack_quantity receive_quantity return_quantity status_in_sequence
10001 receive 05/01/2010 05/02/2010 06/02/2010 07/01/2010 null 10 10 10 10 null 4
10002 return 05/01/2011 06/01/2011 06/02/2011 07/01/2011 08/01/2011 20 20 20 20 20 5
I am going to get result as below:
order_id status date order_quantity pack_quantity ship_quantity receive_quantity return_quantity
10001 receive 05/01/2010 10 0 0 0 0
10001 receive 05/02/2010 0 10 0 0 0
10001 receive 06/02/2010 0 0 10 0 0
10001 receive 07/01/2010 0 0 0 10 0
10002 return 05/01/2011 20 0 0 0 0
10002 return 06/01/2011 0 20 0 0 0
10002 return 06/02/2011 0 0 20 0 0
10002 return 07/01/2011 0 0 0 20 0
10002 return 08/01/2011 0 0 0 0 20
Every order was recorded with date and quantity for each status -- from 'order' to 'returned', in a single line. how to convert a single line to multiple rows based on status sequence number as shown in the last column, as a level connected?
thank you in advance!!
Pat
|
|
|
|
|
|