mardi 1 juin 2021

Random records for a row

I have the following table strvals() with data. I'm looking to randomly choose 2 rows from strval() table and populate table S1 in a loop.

I want something like this

Create table s1(id primary key,strval1, strval2) as 
   select level,random_rec(strvals), random_rec(strvals) 
   from dual 
   connect by level<=10;

The caveat is the column strval1 has to be different THEN strval2 for each row.

Valid output
1, 'AAAA', 'BBBB'
2, 'CCCC', 'BBBB'
3, 'CCCC', 'AAAA'

Not valid

1, 'AAAA', 'AAAA'

Create table strvals(
  strval varchar2(4),
  constraint pk_strval primary key (strval)   
);

insert into strvals 
values(  
 'AAAA'   
);

insert into strvals 
values(  
 'BBBB'   
);

insert into strvals 
values(  
 'CCCC'   
);



Aucun commentaire:

Enregistrer un commentaire