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. too good piece of information, I had come to know about your site from my friend sajid, bangalore,i have read atleast 11 posts of yours by now, and let me tell you, your web-page gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new post, once again hats off to you! Thanks a lot once again, Regards, obiee online training

    ReplyDelete
  2. 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

  3. Thank you provide valuable informations and iam seacrching same informations,and saved my time OBIEE Online Training

    ReplyDelete

  4. Thank you provide valuable informations and iam seacrching same informations,and saved my time SAS Online Training

    ReplyDelete
  5. 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
  6. 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
  7. hello this formula not work on SQL DB
    can you please write SQL formula

    ReplyDelete
  8. 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
  9. 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

  10. 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