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


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


  3. 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);


Leave a Reply

Your email address will not be published.