Friday, June 22, 2012

OBIEE - Converting Rows into Column values


This post will cover how to convert rows into columns. In OBIEE to convert rows into column I usually use user defined function and call it using Evaluate as shown here.

Oracle database 11g R2 introduced a new predefined analytic function LISTAGG, if you are in 11gR2 database you can use this function for string aggregation.

Sample dataset with department and its employees
 
Department      Employee
----------      ----------
Marketing       Michael
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,Michael
Purchasing      Sigal,Shelli,Karen,Guy,Den,Alexander

In OBIEE we can useLISTAGG function using EVALUATE_AGGR

EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',TableName.ColumnName,',',TableName.ColumnName)



So it is doing comma separation but not returing the full value.

Just use CAST function to set the dataset length

cast(EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',"Department"."Employee Name",',',"Department"."Employee Name") as char(50))

Now you'll get the full value



In my case I know the size so given 50, in places we do not know the output size give some big value.

Vino

11 comments:

  1. interesting piece of information, I had come to know about your web-page from my friend pramod, jaipur,i have read atleast eight posts of yours by now, and let me tell you, your blog gives the best and the most interesting informationRegards, obiee training institutes in hyderebad

    ReplyDelete
    Replies
    1. could you suggest how to do reverse process. I have data with commas but in obiee prompt i need to show individual names. thanks in advance

      Delete
  2. Hello its not working for me i have two column one is department and 2nd is First name.. i am adding below formula to 2nd column.. Let me know where i am wrong..

    Below is the formula i have use..let me know where i am wrong EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',"Employee"."First Name",',',"Employee"."First Name")
    Just now · Like

    ReplyDelete
  3. Thank you for your valuable inputs.

    Could you please help us how to achieve the same function in OBIEE with Teradata.

    Thanks in advance.

    Regards,
    Gopi

    ReplyDelete
  4. hello this formula not work on SQL DB
    can you please write SQL formula

    ReplyDelete
  5. Please use the below formula . it will work .


    cast(EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',"Department"."Employee Name",',',"Department"."Name") as char(50))

    ReplyDelete
  6. cast(EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',"Product"."SKU Name",',',"Product"."SKU Name") as char(50)).It shows some error ,any change required in this

    ReplyDelete

  7. hi Christin , why are using Product"."SKU Name" twice , you should use it once and a different column on top of you are going to aggregate it

    ReplyDelete
  8. This is amazing - thank you so much!

    ReplyDelete
  9. is it possible with semicolon

    ReplyDelete
  10. I get the below error.
    EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)
    Should we ask Oracle to set this or can we do this ourself? Any leads?

    ReplyDelete