Pasted

    SELECT 
      CLM_LN_PID,
      DX_SCHEME_CODE,
      rpad(CAST (( rpad(DX_CODE_1, 7, ' ') || rpad(DX_CODE_2, 7, ' ') || rpad(DX_CODE_3, 7, ' ') || rpad(DX_CODE_4, 7, ' ')
        || RPAD(DX_CODE_5, 7, ' ') || RPAD(DX_CODE_6, 7, ' ') || RPAD(DX_CODE_7, 7, ' ') || RPAD(DX_CODE_8, 7, ' ')
        || RPAD(DX_CODE_9, 7, ' ') || RPAD(DX_CODE_10, 7, ' ') || RPAD(DX_CODE_11, 7, ' ') || RPAD(DX_CODE_12, 7, ' ')
        || RPAD(DX_CODE_13, 7, ' ') ) AS VARCHAR2(91)), 91, ' ') AS INMED_DIAGNOSES
    FROM (
      SELECT
        cl.CLM_LN_PID,
        dx.DX_CODE,
        dxs.DX_SCHEME_CODE,
        CLD.CLM_LN_DX_NBR
      FROM midr.clm_ln cl
      JOIN midr.CLM_LN_DX cld
        ON cl.CLM_LN_PID = cld.CLM_LN_PID
      JOIN midr.dx dx
        ON cld.DX_PID = dx.DX_PID
      JOIN MIDR.DX_SCHEME DXS
        ON DX.DX_SCHEME_PID = DXS.DX_SCHEME_PID
    ) PIVOT (MIN(DX_CODE) FOR CLM_LN_DX_NBR IN ( 1 AS DX_CODE_1, 2 AS DX_CODE_2, 3 AS DX_CODE_3, 4 AS DX_CODE_4, 
      5 AS DX_CODE_5, 6 AS DX_CODE_6, 7 AS DX_CODE_7, 8 AS DX_CODE_8, 9 AS DX_CODE_9, 10 AS DX_CODE_10, 
      11 AS DX_CODE_11, 12 AS DX_CODE_12, 13 AS DX_CODE_13));