sql query to format row to column

Consider these two tables,


Table:1
LOAN_NO      REASON_CODE
 100                        A
 100                        B
 200                       A
 300                       A
 300                       B

Table:2

LOAN_NO  REASON_CODE DESCRIPTION
  100                        A                    XYZ
  100                        A                    YSN
  100                        A                    dfsdf
  100                        B                     HJBNDJ

Expected result:

LOAN_NO      DESCRIPTION_A     DESCRIPTION_B
 100                           XYZ                         HJBNDJ
 100                           YSN                       
 100                          dfsdf

Here in the table 1, reason code A or B and both will be inserted only once.
Table 2, will have any number of descriptions under 'A' followed by 'B' inserted row by row.

In the expected resultset, we want all the A descriptions and B descriptions to be displayed parallelly to each other , like may A has 10 descriptions and B has only 2 or vice versa. Here instead of telling A and B, we are using lender error message and DU error message.

SELECT

LOAN.LOAN_NO, /*Arrangement*/
LOAN.CREATE_DT, /*AUS Submitted Date*/
CHANNEL.TAKEN_CD, /*Channel type*/
LOAN_ADDL.PROP_SELECTED_YN, /*Application type*/
DECODE(TEMP.ROW_NO,TEMP.DU_ROW_NO,TEMP.DU_EMSG)DU_ERROR_MSG, /*DU Error message*/
DECODE(TEMP.ROW_NO,TEMP.LENDER_ROW_NO,TEMP.LENDER_EMSG)LENDER_ERROR_MSG /*Lender Error message*/


FROM


/*Inline view contains the logic to obtain the resultset of DU Error message and Lender Error Message parallel to each other for each an arrangement */

(SELECT
NVL(DU.LOAN_NUMBER,LENDER.LOAN_NUMBER)TEMP_LOAN_NO,
DU.AUS_ERROR_MSG DU_EMSG,
LENDER.AUS_ERROR_MSG LENDER_EMSG,
DU.DU_ROW_NO,LENDER.LENDER_ROW_NO,
/*Function to join the DU and Lender Error messages each other based on row number that obtain from the child inline views */
ROW_NUMBER()OVER(PARTITION BY NVL(DU.LOAN_NUMBER,LENDER.LOAN_NUMBER)ORDER BY NVL(DU.LOAN_NUMBER,LENDER.LOAN_NUMBER),DU.DU_ROW_NO,LENDER.LENDER_ROW_NO)ROW_NO


FROM

/*Child inline view for DU Error messages*/
(SELECT
LOAN_ERROR.AUS_ERROR_MSG,
LOAN_AUS.LOAN_AUS_LOG_ID,
LOAN_AUS.LOAN_NUMBER ,
/*Function to assigning row number to the error messages */
ROW_NUMBER()OVER(PARTITION BY LOAN_AUS.LOAN_NUMBER
ORDER BY LOAN_AUS.LOAN_NUMBER,LOAN_ERROR.AUS_ERROR_MSG)DU_ROW_NO
FROM LOAN_AUS_LOG LOAN_AUS,LOAN_AUS_ERROR_LOG LOAN_ERROR
WHERE LOAN_ERROR.LOAN_AUS_LOG_ID= LOAN_AUS.LOAN_AUS_LOG_ID
AND LOAN_AUS.AUS_CODE='DU'
)DU


FULL OUTER JOIN


/*Child inline view for Lender Error messages*/
(SELECT
LOAN_ERROR.AUS_ERROR_MSG,
LOAN_AUS.LOAN_AUS_LOG_ID,
LOAN_AUS.LOAN_NUMBER ,
/*Function to assigning row number to the error messages */
ROW_NUMBER()OVER(PARTITION BY LOAN_AUS.LOAN_NUMBER
ORDER BY LOAN_AUS.LOAN_NUMBER,LOAN_ERROR.AUS_ERROR_MSG)LENDER_ROW_NO
FROM LOAN_AUS_LOG LOAN_AUS,LOAN_AUS_ERROR_LOG LOAN_ERROR
WHERE LOAN_ERROR.LOAN_AUS_LOG_ID= LOAN_AUS.LOAN_AUS_LOG_ID
AND LOAN_AUS.AUS_CODE='LENDER'
)LENDER


ON DU.DU_ROW_NO=LENDER.LENDER_ROW_NO AND DU.LOAN_NUMBER=LENDER.LOAN_NUMBER)TEMP ,
LOANS LOAN,
MISC1003 CHANNEL,
PT_T_LOANSADDL LOAN_ADDL


WHERE
LOAN.LOAN_NO= LOAN_ADDL.LOAN_NO
AND LOAN_ADDL.LOAN_NO=CHANNEL.LOAN_NO
AND CHANNEL.LOAN_NO=TEMP.TEMP_LOAN_NO
AND LOAN_ADDL.PROP_SELECTED_YN IN ('Y','N') /*Full Application, CCB Arrangements */
AND CHANNEL.TAKEN_CD IN ('I','T') /*Channel, I-Internet, T-Telephone*/
AND TRUNC (LOAN.CREATE_DT) BETWEEN TRUNC(to_date('2010/03/20','yyyy/mm/dd')) AND TRUNC(to_date('2010/03/20','yyyy/mm/dd'))