0 and 1 FROM DUAL Matrix

Question

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

Answer 1

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

Answer 2

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;

EDIT: misread your question

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
;