-2

Input

Student_Name Maths Science Social
A 20 30 10
B 10 20 30

Output

Student_Name max_sub min_sub
A Science Social
B Social 20Maths

I tried

select student_name,max(marks) m1, min(marks) m2
from
(
select student_name, engg, fre, sp from student)
unpivot (marks for subject in(engg, fre, sp)

) 
group by student_name;

but no luck

APC
  • 144,005
  • 19
  • 170
  • 281
Martin
  • 1
  • 3
  • Your attempted query shows different "subjects" than you show in your sample data - they are ENGG, FRE, SP rather than MATHS, SCIENCE, SOCIAL. That doesn't look good; if your actual table has ENGG, FRE, SP, then why not show exactly that in your sample, too? –  Apr 29 '22 at 17:31

2 Answers2

0

Unpivot, then re-pivot (or, simpler, just aggregate explicitly, as I show below):

with
  student(student_name, math, science, social) as (
    select 'A', 20, 30, 10 from dual union all
    select 'B', 10, 20, 30 from dual
  )
select  student_name,
        max(sub) keep (dense_rank last  order by mark nulls first) as max_sub,
        min(sub) keep (dense_rank first order by mark nulls last ) as min_sub
from    student
unpivot (mark for sub in (math as 'Math', science as 'Science',
                          social as 'Social'))
group   by student_name
;

STUDENT_NAME MAX_SUB MIN_SUB
------------ ------- -------
A            Science Social 
B            Social  Math

A few things are missing or plain wrong in your problem specification. First and most important, I do hope that in real life you have a student id column - different students may have the same name. (One can work around that for this specific problem, but not in general.)

Then - you need to specify how null grades should be treated (what if a student simply doesn't have a grade in Math, for example), and what to show in the case of ties (a student has the same, highest grade in two different subjects). I made one particular set of choices above - they may or may not be right for your use case. Any other choices can be accommodated easily, as soon as you say what they are.

0

You do not need to UNPIVOT and then PIVOT. You can use a CASE statement and compare the columns values using GREATEST, for the maximum, and LEAST, for the minimum:

SELECT student_name,
       CASE
       WHEN maths   = LEAST(maths, science, social) THEN 'Maths'
       WHEN science = LEAST(maths, science, social) THEN 'Science'
       WHEN social  = LEAST(maths, science, social) THEN 'Social'
       END AS minimum,
       CASE
       WHEN maths   = GREATEST(maths, science, social) THEN 'Maths'
       WHEN science = GREATEST(maths, science, social) THEN 'Science'
       WHEN social  = GREATEST(maths, science, social) THEN 'Social'
       END AS maximum
FROM   student;

Which, for the sample data:

CREATE TABLE student (Student_Name, Maths, Science, Social) AS
SELECT 'A', 20, 30, 10 FROM DUAL UNION ALL
SELECT 'B', 10, 20, 30 FROM DUAL UNION ALL
SELECT 'C', 10, 10, 10 FROM DUAL;

Outputs:

STUDENT_NAME MINIMUM MAXIMUM
A Social Science
B Maths Social
C Maths Maths

If you want to show all the subjects in the case of a tie for minimum or maximum then you can use:

SELECT student_name,
       LTRIM(
            CASE WHEN maths   = LEAST(maths, science, social) THEN 'Maths' END
         || CASE WHEN science = LEAST(maths, science, social) THEN ', Science' END
         || CASE WHEN social  = LEAST(maths, science, social) THEN ', Social' END,
         ', '
       ) AS minimum,
       LTRIM(
            CASE WHEN maths   = GREATEST(maths, science, social) THEN 'Maths' END
         || CASE WHEN science = GREATEST(maths, science, social) THEN ', Science' END
         || CASE WHEN social  = GREATEST(maths, science, social) THEN ', Social' END,
         ', '
       ) AS maximum
FROM   student;

Which outputs:

STUDENT_NAME MINIMUM MAXIMUM
A Social Science
B Maths Social
C Maths, Science, Social Maths, Science, Social

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117