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 ?


      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.