How to divide fields of a table with SQL?

Question

How to divide fields of a table with SQL?

Navigation

#1 by (2 votes)

1

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:

sql oracle

asked by Andres Felipe Polo 03.02.2018 в 01:15

source

1 answer

Use of undefined constant length - assumed 'length' Node -npm start npm ERR! missing script: start

score 2 · Accepted 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

select
codigo,
curso,
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.