Input

Output

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

CodePudding user response：

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.

CodePudding user response：

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