OBIEE - Converting Rows data into one Column values

                                                                                                                                                                                Sometimes I get the requirement 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.