SELECT 

CASE WHEN ROW_NUMBER=1 THEN

DATA

ELSE TO_CLOB('See Description above') END DATA,



CH_DOC_ID,

COUNT_ID,

COUNT_ID_2,

COUNT_ID_3,

ROW_NUMBER,

CH_UID,

CH_DOC_TYPE,

STATUS,

TOOL,

TITLE,

ORIGINATOR,

CREATE_DATE,



CH_DOC_ID_1,

CH_UID_1,

CH_DOC_TYPE_1,

STATUS_1,

TOOL_1 ,

DROP_TRIGGER_1,



CH_DOC_ID_2,

CH_UID_2,

FILE_VERSION_2,



CH_DOC_ID_3,

CH_UID_3,

FILE_VERSION_3,

DATA_3,



CH_DOC_ID_4,

CH_UID_4,

COALESCE(FILE_VERSION_4, 'No Value Found'),

COALESCE(DATA_4,TO_CLOB( 'No Value Found')),



CH_DOC_ID_5,

CH_UID_5,

FILE_VERSION_5,

DATA_5,



CH_DOC_ID_6,

CH_UID_6,

COALESCE(FILE_VERSION_6,'No Value Found'),

COALESCE(DATA_6,TO_CLOB( 'No Value Found'))



 FROM

(

SELECT PCMS_CHDOC_DATA.CH_DOC_ID,



              COUNT(PCMS_CHDOC_DATA_2.CH_DOC_ID) OVER (PARTITION BY PCMS_CHDOC_DATA.CH_DOC_ID) COUNT_ID,

              COUNT(DISTINCT PCMS_CHDOC_DATA.CH_DOC_ID) OVER () COUNT_ID_2,

              COUNT(DISTINCT PCMS_CHDOC_DATA_2.CH_DOC_ID) OVER () COUNT_ID_3,



              ROW_NUMBER() OVER (PARTITION BY PCMS_CHDOC_DATA.CH_DOC_ID ORDER BY PCMS_CHDOC_DATA_2.CH_DOC_ID) ROW_NUMBER,



               PCMS_CHDOC_DATA.CH_UID,

               PCMS_CHDOC_DATA.CH_DOC_TYPE,

               PCMS_CHDOC_DATA.STATUS,
etc......