How to divide fields of a table with SQL?


I have an entity or database table with records like the following:

I am aware that the entity needs database normalization but at the moment I only have read permissions and I am not allowed to make modifications of this type, which would obviously be the best.

What I want is to make a query (Query) that allows me to eliminate the redundancy that exists in those registers so that I can obtain a unique record for each COURSE with three new fields that represent the number of the period and its QUALIFICATION. Getting a result like this:

asked by Andres Felipe Polo 03.02.2018 в 01:15

1 answer


I'm not very familiar with oracle and its syntax for pivot . But this code resolves:

create table calificacion(
  codigo integer,
  curso varchar(32), 
  periodo integer,
  calificacion decimal(10,2));

insert into calificacion values(1, 'matematica', 1, 3.1);
insert into calificacion values(1, 'matematica', 2, 5);
insert into calificacion values(1, 'matematica', 3, 0);
insert into calificacion values(2, 'inglés', 1, 4.4);
insert into calificacion values(2, 'inglés', 2, 3.1);
insert into calificacion values(2, 'inglés', 3, 3.3);

And the next query would give you the desired result

sum(case when periodo = 1 then calificacion else 0 end) as Periodo1,
sum(case when periodo = 2 then calificacion else 0 end) as Periodo2,
sum(case when periodo = 3 then calificacion else 0 end) as Periodo3
from calificacion
group by codigo, curso
order by codigo

I leave you a demo with the previous code.

answered by 03.02.2018 / 03:09