# Report in APEX

The other day someone at a customer had a question about displaying a report. The different options are in a table so he could easily use a report to display the options. But he wanted to display three columns of options. That is, if there are 10 options, he would want to display 3 rows of 3 options and a single row of 1 option.

 option 1 option 2 option 3 option 4 option 5 option 6 option 7 option 8 option 9 option 10

should be displayed as:

 option 1 option 2 option 3 option 4 option 5 option 6 option 7 option 8 option 9 option 10

How can this be done. The answer is in the query.

I decided to try some stuff and came up with the solution. First I need about 10 records.

SELECT rownum rn FROM user_source WHERE rownum < 11;

 1 2 3 4 5 6 7 8 9 10

Using subquery factoring helps me build the query step by step. Next, I need to split the results into three groups. Using the MOD function I can tell in which group a record should reside.

WITH t AS

(SELECT rownum rn FROM user_source WHERE rownum < 11)

select MOD(rn,3) rn2

,rn from t;

 1 1 2 2 0 3 1 4 2 5 0 6 1 7 2 8 0 9 1 10

Now I want to split the results into different sets. Let’s begin by creating a set of the first records.

WITH t AS

(SELECT rownum rn FROM user_source WHERE rownum < 11),

x AS

(SELECT MOD(rn

,3) rn2

,rn

FROM t)

SELECT rn     v1

,rownum r1

FROM x

WHERE rn2 = 1;

 1 1 4 2 7 3 10 4

If I create three set of data (unique) then I can join them again

WITH t AS

(SELECT rownum rn FROM user_source WHERE rownum < 11),

x AS

(SELECT MOD(rn,3) rn2

,rn

FROM t),

all1 AS

(SELECT rn     v1

,rownum r1

FROM x

WHERE rn2 = 1),

all2 AS

(SELECT rn     v2

,rownum r2

FROM x

WHERE rn2 = 2),

all3 AS

(SELECT rn     v3

,rownum r3

FROM x

WHERE rn2 = 0)

SELECT v1

,v2

,v3

FROM all1

JOIN all2 ON (r1 = r2)

JOIN all3 ON (r2 = r3);

 1 2 3 4 5 6 7 8 9

What happened to number 10? The records don’t always exist in the second and third set, so I have to use an outer join to see all the records available.

WITH t AS

(SELECT rownum rn FROM user_source WHERE rownum < 11),

x AS

(SELECT MOD(rn,3) rn2

,rn

FROM t),

all1 AS

(SELECT rn     v1

,rownum r1

FROM x

WHERE rn2 = 1),

all2 AS

(SELECT rn     v2

,rownum r2

FROM x

WHERE rn2 = 2),

all3 AS

(SELECT rn     v3

,rownum r3

FROM x

WHERE rn2 = 0)

SELECT v1

,v2

,v3

FROM all1

LEFT OUTER JOIN all2 ON (r1 = r2)

LEFT OUTER JOIN all3 ON (r2 = r3);

 1 2 3 4 5 6 7 8 9 10

Now the result is exactly the way I (or actually the customer) wanted. All that needs to be done now is adapt this query so that it uses the real data. But the idea is in place.

## 4 thoughts on “Report in APEX”

1. Thanks!
Nice exercise for weekend. I’ve been searching for something like that to add something to my primitive SQL skills.

2. Hi!
This is a classical pivot requirement. Before 11g, you could write something like this:

create table t_opt as
select level o#
from dual connect by level <= 10
/
select
trunc((o#-1)/3),
min(case when mod(o#,3)=1 then o# else null end) col1,
min(case when mod(o#,3)=2 then o# else null end) col2,
min(case when mod(o#,3)=0 then o# else null end) col3
from
(select rownum o#
from t_opt)
group by trunc((o#-1)/3)
order by 1
/

TRUNC((O#-1)/3) COL1 COL2 COL3
————— ———- ———- ———-
0 1 2 3
1 4 5 6
2 7 8 9
3 10

3. Oops, a copy/paste error. I wanted to write

select
trunc((o#-1)/3),
min(case when mod(o#,3)=1 then o# else null end) col1,
min(case when mod(o#,3)=2 then o# else null end) col2,
min(case when mod(o#,3)=0 then o# else null end) col3
from
(select level o#
from dual
connect by level <= 10)
group by trunc((o#-1)/3)
order by 1
/

4. TV

Maybe overkill, but here’s another way to do this…

CREATE OR REPLACE TYPE r_rec IS OBJECT
(col1 VARCHAR2(100)
,col2 VARCHAR2(100)
,col3 VARCHAR2(100)
)
/
CREATE OR REPLACE TYPE t_rec IS TABLE OF r_rec
/

CREATE OR REPLACE FUNCTION three_columns RETURN t_rec
PIPELINED IS

CURSOR c_cur IS
SELECT rownum rn
FROM user_source
WHERE rownum < 11;
rec r_rec := r_rec(NULL, NULL, NULL);
i INTEGER := 1;

BEGIN

FOR l_cur IN c_cur
LOOP
CASE i
WHEN 1 THEN
rec.col1 := l_cur.rn;
WHEN 2 THEN
rec.col2 := l_cur.rn;
WHEN 3 THEN
rec.col3 := l_cur.rn;
PIPE ROW(rec);
rec := r_rec(NULL, NULL, NULL);
i := 0;
END CASE;
i := i + 1;
END LOOP;
IF i 3
THEN
PIPE ROW(rec);
END IF;
RETURN;

END three_columns;

/
SELECT * from TABLE (three_columns);