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.
Thanks!
Nice exercise for weekend. I’ve been searching for something like that to add something to my primitive SQL skills.
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
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
/
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);