8 Replies Latest reply on Dec 4, 2015 11:08 AM by Rohit_L

    How To get Distinct Records using ListAgg function in OBIEE Report ?

    Rohit_L

      Hello All,

       

      I am getting correct result like mention below. But I don't want to see duplicates in my result Here in my example I am getting duplicate record for Employee Name 'Pat'. So How to get Distinct values using LISTAGG function ?

       

      Sample data set with department and its employees

       

      Department      Employee

      ----------      ----------

      Marketing       Michael

      Marketing       Pat

      Marketing      Pat

      Marketing     Pat

      Purchasing      Den

      Purchasing      Alexander

      Purchasing      Shelli

      Purchasing      Sigal

      Purchasing      Guy

      Purchasing      Karen

       

      Using ListAgg function we can convert this into :

       

      Department      Employees

      -----------     -------------------------------------

      Marketing       Pat,Pat,Pat,Michael

      Purchasing      Sigal,Shelli,Karen,Guy,Den,Alexander

       

      I tried lots of things but I am not able to figure out how exactly this can be achieved, If anyone have any idea or suggestions please share, thanks in advance.