SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Pivot and Case Statement(?

Daniel RichardJan 24 2025

I have a table that is part of a corporate database, so I can't modify it. The table looks something like this:

<table><tbody><tr><td style="height:10.2pt;width:52pt;">ITEM</td><td style="width:73pt;">DATE_ORDERED</td><td style="width:77pt;">ORDER#</td><td style="width:98pt;">DATE_PROCESSED</td></tr><tr><td style="height:10.2pt;">ABCD</td><td>11-Dec-24</td><td>IN-1345</td><td>12-Dec-24</td></tr><tr><td style="height:10.2pt;">ABCD</td><td>11-Dec-24</td><td>OUT-1234</td><td>12-Jan-25</td></tr><tr><td style="height:10.2pt;">EFGH</td><td>06-Sep-24</td><td>IN-1456</td><td>06-Sep-24</td></tr><tr><td style="height:10.2pt;">EFGH</td><td>06-Sep-24</td><td>OUT-1567</td><td>08-Sep-24</td></tr><tr><td style="height:10.2pt;">IJLK</td><td>22-Mar-24</td><td>IN-2345</td><td>22-Mar-24</td></tr><tr><td style="height:10.2pt;">MNOP</td><td>02-May-24</td><td>IN-1234</td><td>10-May-24</td></tr></tbody></table>

Simply stated, I want the “IN” order numbers in a column called "ORDER IN" and the “Out” order numbers in “ORDER OUT”. The same sort of thing with the dates. Something like this:

<table><tbody><tr><td style="height:10.2pt;width:52pt;">ITEM</td><td style="width:73pt;">DATE_ORDERED</td><td style="width:77pt;">ORDER_IN</td><td style="width:98pt;">DATE_PROCESSED_IN</td><td style="width:74pt;">ORDER_OUT</td><td style="width:103pt;">DATE_PROCESSED_OUT</td></tr><tr><td style="height:10.2pt;">ABCD</td><td>11-Dec-24</td><td>IN-1345</td><td>12-Dec-24</td><td>OUT-1234</td><td>12-Jan-25</td></tr><tr><td style="height:10.2pt;">EFGH</td><td>06-Sep-24</td><td>IN-1456</td><td>06-Sep-24</td><td>OUT-1567</td><td>08-Sep-24</td></tr><tr><td style="height:10.2pt;">IJLK</td><td>22-Mar-24</td><td>IN-2345</td><td>22-Mar-24</td><td> </td><td> </td></tr><tr><td style="height:10.2pt;">MNOP</td><td>02-May-24</td><td>IN-1234</td><td>10-May-24</td><td> </td><td> </td></tr></tbody></table>

The order numbers always start with IN or OUT, but there can be duplicates in the sequence number (e.g. : IN-1234 and OUT-1234)

I am thinking some sort of pivot and a case clause in the pivot?

Added on Jan 24 2025

#pl-sql

2 comments

337 views

1 mention