Home » SQL & PL/SQL » SQL & PL/SQL » how to write this unpivot query (oracle 12c. )
how to write this unpivot query [message #642487] Fri, 11 September 2015 14:48 Go to next message
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
Re: how to write this unpivot query [message #642488 is a reply to message #642487] Fri, 11 September 2015 14:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: how to write this unpivot query [message #642489 is a reply to message #642488] Fri, 11 September 2015 15:06 Go to previous messageGo to next message
zeninblue
Messages: 2
Registered: September 2015
Junior Member
edit it with [code] to make the post neat. Thanks Blackswan.

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


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
Re: how to write this unpivot query [message #642492 is a reply to message #642489] Fri, 11 September 2015 19:18 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/3791159
Previous Topic: String longer than 4000 chars inside cursor-for loop
Next Topic: String Comparasition
Goto Forum:
  


Current Time: Thu Mar 28 05:32:12 CDT 2024