How to Convert Rows  into  Columns value OBIEE 11g: 

Hi All,

Today i am going to explain how to convert the rows into the column in OBIEE Reporting. We can perform this activity at report level.

For this we will be using the Evaluate_AGGR Function and Listagg Function.

Below is the sample table having Cust Table .

                 Cust 

Location	Customer
U.S	Alex
U.S	Brij
U.S	Jack
China	Xing
China	Li Yi
China	Chang

Expected Output:

Location          Customers 
U.S               Alex,Brij,Jack,
China             Xing,Li Yi,Chang


So we will be using the Evaluate_AGGR and Listagg to convert the row values into the singe column value which will be separated by the comma's .

EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',"Cust"."Customer" ,',',"Cust"."Customer")

If you are not getitng the all name of cutomers then we have to increase the clumn length

Cast(EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',"Cust"."Customer" ,',',"Cust"."Customer") as char(100)) 

In the Next Session i will cover how to convert these comma repeated values into different -different column values .

Comments are appreciated.