Business Intelligence – Oracle

Archive for February 12th, 2008

Oracle BI EE 10.1.3.3/2 – Pivoting Strings – Converting Rows into Columns

Posted by Venkatakrishnan J on February 12, 2008

A pretty interesting question came up today in one of our internal forums. I just thought i would share with you all since BI EE does not have this capability out of the box. Look at the screenshot below.

      

The above picture gives you a simple report containing REGION_NAME and COUNTRY_NAME columns. Now the question was how do we convert this report into the one shown below

Africa      South Africa
Americas   Argentina, Brazil, Canada, United States of America
Asia   India, Japan, Singaport
………….
…………

So, basically, the idea is to have a report containing all the list of countries under a region in a single row where each country within a region has to be concatenated using commas. In order to do this, we shall see 2 approaches today.

1.   Using a database function and then calling this function from BI EE using EVALUATE.

2.   Using Direct Database Requests (not recommended).

Lets look at both the approaches one by one. For the 1st one, lets create a database function as shown below

CREATE OR REPLACE FUNCTION PIVOT_OP(p_REGION IN VARCHAR2)
RETURN VARCHAR2
IS
v_region varchar2(1000);
v_geography varchar2(1000);
BEGIN
select
REGION_NAME,
RTRIM(LTRIM(SYS_CONNECT_BY_PATH(COUNTRY_NAME, ‘,’),’,’),’,’) into v_region, v_geography
from
(
select
COUNTRY_NAME,
REGION_NAME,
count(*) OVER ( partition by REGION_NAME ) rowcnt,
ROW_NUMBER () OVER ( partition by REGION_NAME order by COUNTRY_NAME) seqno
from
(select distinct
REGION_NAME,
COUNTRY_NAME from GEOGRAPHY)
where
REGION_NAME is not null)
where
seqno=rowcnt and
REGION_NAME = p_REGION
start with
seqno=1
connect by prior
seqno+1=seqno
and prior
REGION_NAME=REGION_NAME;
RETURN v_geography;
END PIVOT_OP;

What this function basically does is, it does a recursive concatenation of all the countries in a region using the SYS_CONNECT_BY_PATH(this would work from 10g database or above). Check whether the function is working properly.

      

Now the next step is to call this from Answers using EVALUATE. Ensure that the above function is compiled in the same schema from which the source data is coming from.

      

Now you should get what is required in your final report.

      

Lets go to the second approach. In this approach, we will be using Direct Database requests. Remember, this is not recommended at all. I have included it here just for demonstration purposes. Use the following query (change it to your needs).

select
REGION_NAME ,
RTRIM(LTRIM(SYS_CONNECT_BY_PATH(COUNTRY_NAME, ‘,’),’,’),’,’) Countries
from
(
select
COUNTRY_NAME,
REGION_NAME,
count(*) OVER ( partition by REGION_NAME ) rowcnt,
ROW_NUMBER () OVER ( partition by REGION_NAME order by COUNTRY_NAME) seqno
from
(select distinct
REGION_NAME,
COUNTRY_NAME from GEOGRAPHY)
where
REGION_NAME is not null)
where
seqno=rowcnt
start with
seqno=1
connect by prior
seqno+1=seqno
and prior
REGION_NAME=REGION_NAME

      

      

Advertisements

Posted in All Posts, OBI EE Plus | 5 Comments »