Report in APEX



By Patch ~ April 16th, 2010. Filed under: APEX, Oracle, SQL.

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.

Reader's Comments

  1. agnatio | April 17th, 2010 at 3:17 pm

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

  2. Chris Neumueller | April 18th, 2010 at 9:28 am

    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. Chris Neumueller | April 18th, 2010 at 9:32 am

    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 | April 21st, 2010 at 5:06 pm

    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 Comment