asked on

oracle sql analytics

i have a table which records students preferences. i need to flag up the students who have only preferenced 20, 21 or 22.
the table below shows the output from the pref table (2 columns), and the output shows the desired output.

select * from pref			output					
								
student	preference		student	only_20_21_22				
1		1				1		n
1		2				2		n
1		3				3		y
2		1				4		n
2		2				5		y
3		20				6		n
4		21				7		n
4		22				8		y
4		1				9		n
4		2				10		n
5		20						
5		22						
5		21						
6		1						
6		2						
7		1						
7		20						
8		20						
9		20						
9		1						
9		2						
9		22						
10		2

Open in new window

any help is appreciated

Ahmed Merghani

Try this:

Select student, CASE 
            WHEN preference = 20 or preference = 21 or preference = 22 
               THEN 'y' 
               ELSE 'n' 
       END as only_20_21_22 from pref

Open in new window

Alex [***Alex140181***]

select p.student,
       case regexp_count(listagg(p.preference, ',') within group(order by p.student), '^20|^21|^22')
         when 1 then
          'y'
         else
          'n'
       end only_20_21_22
  from pref p
 group by p.student
 order by p.student;

Open in new window

Alex [***Alex140181***]

@Ahmed Merghani: Please see the asker's expected results! Your statement will not work upon what he asked for...

SOLUTION