# 0 and 1 FROM DUAL Matrix

I need to make a matrix with 0 and 1 combination :

``````SELECT 1 AS F1, 0 AS F2 ,0 AS F3,0 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 1 AS F2 ,0 AS F3,0 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 0 AS F2 ,1 AS F3,0 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 0 AS F2 ,0 AS F3,1 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 0 AS F2 ,0 AS F3,0 AS F4, 1 AS F5 FROM DUAL
``````

Do you have some code more tricky or elegant to achieve this ?

I'm on Oracle 19c

Thx

• If you want to Name the columns it is Sufficient to do this in the first select Sep 19, 2022 at 20:02
• @miracle173 yes absolutely, it's was here just a lazy copy/paste ^^
– Nan0
Sep 20, 2022 at 4:57

Three more methods, one that uses the function `SYS.ODCINUMBERLIST()` and `TABLE()` to produce a 5-rows table, a second that uses `CONNECT BY` (Oracle's old method for recursive queries) and a third that abuses `GROUPING SETS`.

Note that `TABLE()` is not needed in recent versions. In the first query, we could just use:

``````from SYS.ODCINUMBERLIST(1, 2, 3, 4, 5) ;
``````

Tested in dbfiddle.uk:

``````select * from V\$VERSION;
``````
BANNER BANNER_FULL BANNER_LEGACY CON_ID
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production 0
``````select
case when column_value  = 1 then 1 else 0 end as f1,
case when column_value  = 2 then 1 else 0 end as f2,
case when column_value  = 3 then 1 else 0 end as f3,
case when column_value  = 4 then 1 else 0 end as f4,
case when column_value  = 5 then 1 else 0 end as f5
from table(
SYS.ODCINUMBERLIST(1, 2, 3, 4, 5)
) ;
``````
F1 F2 F3 F4 F5
1 0 0 0 0
0 1 0 0 0
0 0 1 0 0
0 0 0 1 0
0 0 0 0 1
``````select case when rownum = 1 then 1 else 0 end as F1
, case when rownum = 2 then 1 else 0 end as F2
, case when rownum = 3 then 1 else 0 end as F3
, case when rownum = 4 then 1 else 0 end as F4
, case when rownum = 5 then 1 else 0 end as F5
from  dual
connect by level <= 5 ;
``````
F1 F2 F3 F4 F5
1 0 0 0 0
0 1 0 0 0
0 0 1 0 0
0 0 0 1 0
0 0 0 0 1
``````select case when rownum = 1 then 1 else 0 end as F1
, case when rownum = 2 then 1 else 0 end as F2
, case when rownum = 3 then 1 else 0 end as F3
, case when rownum = 4 then 1 else 0 end as F4
, case when rownum = 5 then 1 else 0 end as F5
from
( select count(*) from dual
group by grouping sets
((), (), (), (), ())  -- 5 groupings
) t ;
``````
F1 F2 F3 F4 F5
1 0 0 0 0
0 1 0 0 0
0 0 1 0 0
0 0 0 1 0
0 0 0 0 1

fiddle

• thanks for sharing ! never heard about those ODCI% array, I will take a look at Oracle's documentation ^^
– Nan0
Sep 19, 2022 at 10:43

Instead of creating the combinations your self, you can let the DB do it for you:

``````with d(x) as ( select 0 from dual union all select 1 from dual )
select d1.x as F1
, d2.x as F2
, d3.x as F3
, d4.x as F4
, d5.x as F5
from d d1
cross join d d2
cross join d d3
cross join d d4
cross join d d5;
``````

``````with d(x) as ( select 0 from dual union all select 1 from dual )
select case when rownum = 1 then 1 else 0 end as F1
, case when rownum = 2 then 1 else 0 end as F2
, case when rownum = 3 then 1 else 0 end as F3
, case when rownum = 4 then 1 else 0 end as F4
, case when rownum = 5 then 1 else 0 end as F5
from d d1
cross join d d2
cross join d d3
order by rownum
fetch first 5 rows only
;
``````

Instead of cross joining a table with 2 rows, it is also possible to use group by cube:

``````with d(x) as ( select 0 from dual group by cube(1,1,1) )
select case when rownum = 1 then 1 else 0 end as F1
, case when rownum = 2 then 1 else 0 end as F2
, case when rownum = 3 then 1 else 0 end as F3
, case when rownum = 4 then 1 else 0 end as F4
, case when rownum = 5 then 1 else 0 end as F5
from d d1
order by rownum
fetch first 5 rows only
;
``````
• thx for this one !
– Nan0
Sep 19, 2022 at 9:45