Tuesday, August 6, 2013

Oracle Group by a String column by string concatenation of its values

CREATE OR REPLACE VIEW SOWCUSTOMERPROJECTVIEW
AS
  SELECT OPPRTNTY.CUST_ACNT_NBR,
    OPPRTNTY.OPPRTNTY_ID,
    OPPRTNTY.EXT_OPPRTNTY_ID,
    OPPRTNTY.OPPRTNTY_NM,
    OPPRTNTY.OPPRTNTY_DSC,
    LISTAGG(PROJECT.PROJECT_NM, ', ') WITHIN GROUP (
  ORDER BY PROJECT.PROJECT_NM) AS SUB_PRODUCT,
    LISTAGG(BATCH_CLASS.BATCH_CLASS_NM, ', ') WITHIN GROUP (
  ORDER BY BATCH_CLASS.BATCH_CLASS_NM) AS SUB_PRODUCT2,
    --PROJECT.PROJECT_NM,
    --BATCH_CLASS.BATCH_CLASS_NM,
    COUNT(PROJECT.PROJECT_ID) AS cnt
  FROM OPPRTNTY
  INNER JOIN PROJECT
  ON OPPRTNTY.OPPRTNTY_ID = PROJECT.OPPRTNTY_ID
  INNER JOIN BATCH_CLASS
  ON BATCH_CLASS.BATCH_CLASS_ID = PROJECT.BATCH_CLASS_ID
  GROUP BY OPPRTNTY.CUST_ACNT_NBR,
    OPPRTNTY.OPPRTNTY_ID,
    OPPRTNTY.EXT_OPPRTNTY_ID,
    OPPRTNTY.OPPRTNTY_NM,
    OPPRTNTY.OPPRTNTY_DSC;
  --PROJECT.PROJECT_NM,

  --BATCH_CLASS.BATCH_CLASS_NM ;

No comments:

Post a Comment