Конвертировать несколько частичных столбцов в одну строку в запросе оракула

У меня есть таблица со структурой ниже

Student:    Studentno   Studentname Grade    School

               1         aaaaa       3      cambridge
               2         bbbbb       4      edison
               3         ccccc       5      concord

mark:    Studentno   subject mark
            1        maths     55
            1        science   23
            1        history   99
            2        english    89
            2        maths   78
            2        history   100
            3        history     77
            3        science   82
            3        maths   78

Мне нужен результат, как показано ниже.

Studentno Studentname Grade School  subject1 mark1 subject2 mark2 suject3 mark3

   1         aaaaa       3  cambridge maths   55     science  23    history  99
   2         bbbbb       4  edison    english   89    maths  78    history  100
   3         ccccc       5  concord   history   77     science  82 maths  78

Я использовал номер строки, но он отображает значения округления, но мне нужно в вышеуказанном формате. Пробовал Pivot, но не знакомый в этом. Нужна помощь

Источник

2 ответа

Вы можете попытаться использовать агрегатную функцию условия после JOIN эти таблицы.

MAX с CASE WHEN

CREATE TABLE Student(
   Studentno INT,
   Studentname VARCHAR(50),
   Grade INT,
   School VARCHAR(50)
);

INSERT INTO Student values (1,'aaaaa',3,'cambridge');
INSERT INTO Student values (2,'bbbbb',4,'edison');
INSERT INTO Student values (3,'ccccc',5,'concord');

CREATE TABLE mark(
   Studentno INT,
   subject VARCHAR(50),
   mark INT
);


insert into mark values (1,'maths',55);
insert into mark values (1,'science',23);
insert into mark values (1,'history',99);
insert into mark values (2,'maths',89);
insert into mark values (2,'science',78);
insert into mark values (2,'history',100);
insert into mark values (3,'maths',77);
insert into mark values (3,'science',82);
insert into mark values (3,'history',78);

Запрос 1:

SELECT s.STUDENTNO,
       s.Studentname,
       s.Grade,
       s.School,
       MAX(CASE WHEN rn= 1 THEN subject END) "subject1",
       MAX(CASE WHEN m.SUBJECT ='maths' THEN m.MARK END)  "Math",
       MAX(CASE WHEN rn= 2 THEN subject END)  "subject2",
       MAX(CASE WHEN m.SUBJECT ='science' THEN m.MARK END)  "science",
       MAX(CASE WHEN rn= 3 THEN subject END)  "subject3",
       MAX(CASE WHEN m.SUBJECT ='history' THEN m.MARK END) "history"
FROM (
  SELECT m.*,ROW_NUMBER() OVER(PARTITION BY STUDENTNO ORDER BY mark) rn
  FROM mark m
) m  
JOIN Student s on m.STUDENTNO = s.STUDENTNO
GROUP BY s.STUDENTNO,
       s.Studentname,
       s.Grade,
       s.School

Результаты:

| STUDENTNO | STUDENTNAME | GRADE |    SCHOOL | subject1 | Math | subject2 | science | subject3 | history |
|-----------|-------------|-------|-----------|----------|------|----------|---------|----------|---------|
|         1 |       aaaaa |     3 | cambridge |  science |   55 |    maths |      23 |  history |      99 |
|         2 |       bbbbb |     4 |    edison |  science |   89 |    maths |      78 |  history |     100 |
|         3 |       ccccc |     5 |   concord |    maths |   77 |  history |      82 |  science |      78 |
Источник

Если есть только 3 предмета, вы можете получить оценки за каждый предмет через подзапрос

SQL

select 
        s.Studentno, s.Studentname, s.Grade, s.School
        ,'maths' as subject1, (select mark from mark m where s.Studentno = m.Studentno and m.subject = 'maths') as mark1
        ,'science' as subject2, (select mark from mark m where s.Studentno = m.Studentno and m.subject = 'science') as mark2
        ,'history' as subject3, (select mark from mark m where s.Studentno = m.Studentno and m.subject = 'history') as mark3
         from Student s

SQL with sample data

with Student as
(
select 1 as Studentno, 'aaaaa' as Studentname, 3 as Grade, 'cambridge' as School from dual union all
select 2 as Studentno, 'bbbbb' as Studentname, 4 as Grade, 'edison' as School from dual union all
select 3 as Studentno, 'ccccc' as Studentname, 5 as Grade, 'concord' as School from dual
),
mark as ( 
select 1 as Studentno, 'maths' as subject, 55 as mark from dual union all
select 1,'science',   23 from dual union all
select 1,'history',   99 from dual union all
select 2,'maths',   89 from dual union all
select 2,'science',   78 from dual union all
select 2,'history',   100 from dual union all
select 3,'maths',   77 from dual union all
select 3,'science',   82 from dual union all
select 3,'history',   78 from dual
)            
select 
s.Studentno, s.Studentname, s.Grade, s.School
,'maths' as subject1, (select mark from mark m where s.Studentno = m.Studentno and m.subject = 'maths') as mark1
,'science' as subject2, (select mark from mark m where s.Studentno = m.Studentno and m.subject = 'science') as mark2
,'history' as subject3, (select mark from mark m where s.Studentno = m.Studentno and m.subject = 'history') as mark3
 from Student s

Result

1   1   aaaaa   3   cambridge   maths   55  science 23  history 99
2   3   ccccc   5   concord maths   77  science 82  history 78
3   2   bbbbb   4   edison  maths   89  science 78  history 100
Источник
Другие вопросы по тегам