CREATE TABLE MSS_RETENTION
(
  CUSTOMER  VARCHAR2(60),
  LIC_NO    VARCHAR2(10),
  CHS_NO    VARCHAR2(25),
  RO_DATE   DATE
)
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;

Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('3S TRANSPORT SERVICES', 'PC2319M', 'JALLT134PC7000044', TO_DATE('03/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('3S TRANSPORT SERVICES', 'PC2420C', 'JALLT134PC7000045', TO_DATE('03/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('3S TRANSPORT SERVICES', 'PC927X', 'JALLT134PB7000013', TO_DATE('02/11/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('800 SUPER WASTE MANAGEMENT PTE LTD', 'XD1390L', 'JALFVR34P77000026', TO_DATE('03/14/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('800 SUPER WASTE MANAGEMENT PTE LTD', 'XD1397T', 'JALFVR34P77000013', TO_DATE('03/14/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('800 SUPER WASTE MANAGEMENT PTE LTD', 'XD1416Y', 'JALFVR34P77000014', TO_DATE('02/20/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('800 SUPER WASTE MANAGEMENT PTE LTD', 'XD2076M', 'JALFVR34P77000043', TO_DATE('03/28/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('800 SUPER WASTE MANAGEMENT PTE LTD', 'XD2473A', 'JALFVR34P87000001', TO_DATE('03/05/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('800 SUPER WASTE MANAGEMENT PTE LTD', 'XD3610P', 'JALCYH52S97000008', TO_DATE('03/18/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('800 SUPER WASTE MANAGEMENT PTE LTD', 'XD3642Y', 'JALCYZ52S97000002', TO_DATE('02/26/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('800 SUPER WASTE MANAGEMENT PTE LTD', 'XD8025X', 'JALCYH52TD7000088', TO_DATE('03/25/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('800 SUPER WASTE MANAGEMENT PTE LTD', 'YM3143L', 'JALFTR33P67000010', TO_DATE('02/14/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('800 SUPER WASTE MANAGEMENT PTE LTD', 'YM3365M', 'JALFTR33P67000007', TO_DATE('01/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('AMERICAN INTERNATIONAL INDUSTRIES (PTE) LTD', 'XD7855Z', 'JALEXR52ED7000066', TO_DATE('01/25/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('AMERICAN INTERNATIONAL INDUSTRIES (PTE) LTD', 'YN1518Y', 'JAANPR75HA7100847', TO_DATE('03/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('AMERICAN INTERNATIONAL INDUSTRIES (PTE) LTD', 'YN1869J', 'JALFVR347B7000045', TO_DATE('02/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('AMERICAN INTERNATIONAL INDUSTRIES (PTE) LTD', 'YN3166R', 'JAANHR85EB7100147', TO_DATE('01/11/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MSS_RETENTION
   (CUSTOMER, LIC_NO, CHS_NO, RO_DATE)
 Values
   ('AMERICAN INTERNATIONAL INDUSTRIES (PTE) LTD', 'YN3256P', 'JALFRR907B7000166', TO_DATE('01/18/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

CREATE OR REPLACE FUNCTION concatenate
  (p_key_name       IN VARCHAR2,
   p_key_value      IN VARCHAR2,
   p_col_to_concat  IN VARCHAR2,
   p_table_name     IN VARCHAR2,
   p_separator      IN VARCHAR2 DEFAULT '*')
  RETURN               VARCHAR2
AS
  TYPE weak_ref_cur IS REF CURSOR;
  v_string             VARCHAR2 (4000);
  v_separator          VARCHAR2 (   3) := NULL;
  v_value              VARCHAR2 (4000);
  v_cur                weak_ref_cur;
BEGIN
  OPEN v_cur FOR 
      'SELECT ' || p_col_to_concat 
  || ' FROM '   || p_table_name 
  || ' WHERE '  || p_key_name || ' = :a'
  || ' ORDER BY :b'
  USING p_key_value, p_col_to_concat;
  LOOP
    FETCH v_cur INTO v_value;
    EXIT WHEN v_cur%NOTFOUND;
      v_string := v_string || v_separator || v_value;
      v_separator := p_separator;
  END LOOP;
  CLOSE v_cur;
  RETURN v_string;
END concatenate;

  SELECT   customer,
           COUNT (CHS_NO) NOV,
           MAX (DECODE (TO_CHAR (RO_DATE, 'MON'), 'JAN', VEHs)) AS "JAN",
           MAX (DECODE (TO_CHAR (RO_DATE, 'MON'), 'FEB', VEHs)) AS "FEB",
           MAX (DECODE (TO_CHAR (RO_DATE, 'MON'), 'MAR', VEHs)) AS "MAR"
    FROM   (SELECT   CUSTOMER,
                     LIC_NO,
                     CHS_NO,
                     RO_DATE,
                     concatenate ('CUSTOMER || LIC_NO',
                                  CUSTOMER || LIC_NO,
                                  'LIC_NO',
                                  'MSS_RETENTION')
                        AS VEHs
              FROM   MSS_RETENTION)
GROUP BY   CUSTOMER;