Mysql svinger roterende rader til kolonner

En databasetabell kan lagre forskjellige typer data, og noen ganger må vi transformere data på radnivå til kolonnivånivå. Dette problemet kan løses ved å bruke Pivot () -funksjonen. Denne funksjonen brukes til å rotere rader av en tabell i kolonneverdier. Men denne funksjonen støttes av veldig få databaseservere, for eksempel Oracle eller SQL Server. Hvis du vil gjøre den samme oppgaven i MySQL -databasetabellen, må du skrive SELECT -spørringen ved å bruke saksuttalelsen for å rotere radene til kolonner. Artikkelen viser måten å gjøre oppgaven til Pivot () -funksjon innen relaterte MySQL -databasetabeller.

Forutsetning:

Du må opprette en database og noen relaterte tabeller der rader med en tabell blir konvertert til kolonnene som Pivot () -funksjon. Kjør følgende SQL -setninger for å opprette en database som heter 'Unidb'og lag tre tabeller som heter'studenter','kurs'Og'resultat'. studenter og resultat Tabeller vil være relatert til en-til-mange forhold og kurs og Resultater Tabeller vil være relatert til en-til-mange-forhold her. Opprett uttalelse av resultat Tabellen inneholder to utenlandske nøkkelbegrensninger for feltene, std_id, og kurs_id.

Opprett database Unidb;
Bruk UNIDB;
Lag bordstudenter (
id int primærnøkkel,
Navn varchar (50) ikke null,
avdelingen varchar (15) ikke null);
Lag bordkurs (
kurs_id varchar (20) primærnøkkel,
Navn varchar (50) ikke null,
kreditt SmallInt ikke null);
Lag tabellresultat (
std_id int ikke null,
kurs_id varchar (20) ikke null,
Mark_type varchar (20) ikke null,
markerer smallint ikke null,
Utenlandsk nøkkel (STD_ID) Referanser Studenter (ID),
Utenlandsk nøkkel (kurs_id) Referanser kurs (kurs_id),
Primærnøkkel (STD_ID, Course_id, Mark_Type));

Sett inn noen poster i studenter, kurs og resultat bord. Verdiene må settes inn i tabellene basert på begrensningene som er satt på tidspunktet for opprettelse av bord.

Sett inn studenters verdier
('1937463', 'Harper Lee', 'CSE'),
('1937464', 'Garcia Marquez', 'CSE'),
('1937465', 'Forster, e.M.',' CSE '),
('1937466', 'Ralph Ellison', 'CSE');
Sett inn kursverdier
('CSE-401', 'Objektorientert programmering', 3),
('CSE-403', 'Datastruktur', 2),
('CSE-407', 'UNIX-programmering', 2);
Sett inn resultatverdier
('1937463', 'CSE-401', 'Intern eksamen', 15),
('1937463', 'CSE-401', 'Mid Term Exam', 20),
('1937463', 'CSE-401', 'Final Exam', 35),
('1937464', 'CSE-403', 'Intern eksamen', 17),
('1937464', 'CSE-403', 'Mid Term Exam', 15),
('1937464', 'CSE-403', 'Final Exam', 30),
('1937465', 'CSE-401', 'Intern eksamen', 18),
('1937465', 'CSE-401', 'Mid Term Exam', 23),
('1937465', 'CSE-401', 'Final Exam', 38),
('1937466', 'CSE-407', 'Intern eksamen', 20),
('1937466', 'CSE-407', 'Mid Term Exam', 22),
('1937466', 'CSE-407', 'avsluttende eksamen', 40);

Her, resultat Tabellen inneholder flere samme verdier for std_id, Mark_Type og kurs_id Kolonner i hver rad. Hvordan konvertere disse radene til kolonner i denne tabellen for å vise dataene i et mer organisert format vises i neste del av denne opplæringen.

Drei rader til kolonner ved hjelp av saksuttalelse:

Kjør følgende enkle valgserklæring for å vise alle poster av resultat bord.

Velg * fra resultatet;

Produksjonen viser de fire studentens merker for tre eksamensstyper på tre kurs. Så verdiene til std_id, kurs_id og Mark_Type gjentas flere ganger for de forskjellige studentene, kursene og eksamensstypene.

Utgangen vil være mer lesbar hvis SELECT -spørringen kan skrives mer effektivt ved å bruke saksuttalelsen. Følgende velger med saksuttalelsen vil transformere de gjentatte verdiene til radene til kolonnens navn og vise innholdet i tabellene i et mer forståelig format for brukeren.

Velg resultat.std_id, resultat.kurs_id,
Maks (sak når resultatet.marker_type = "intern eksamen" deretter resultat.merker slutt) "Intern eksamen",
Maks (sak når resultatet.Mark_Type = "Mid Term Exam" deretter Resultat.merker slutt) "Midt termineksamen",
Maks (sak når resultatet.marker_type = "avsluttende eksamen" deretter resultat.merker slutt) "Avsluttende eksamen"
Fra resultat
Gruppe etter resultat.std_id, resultat.kurs_id
Bestill etter resultat.std_id, resultat.Course_id ASC;

Følgende utgang vises etter å ha kjørt ovennevnte uttalelse som er mer lesbar enn forrige utgang.

Drei rader til kolonner ved hjelp av case og sum ():

Hvis du vil telle det totale antallet av hvert kurs for hver student fra bordet, må du bruke den samlede funksjonen SUM() gruppe av std_id og kurs_id med saksuttalelsen. Følgende spørring opprettes ved å endre den forrige spørringen med sum () -funksjon og gruppe etter klausul.

Velg resultat.std_id, resultat.kurs_id,
Maks (sak når resultatet.marker_type = "intern eksamen" deretter resultat.merker slutt) "Intern eksamen",
Maks (sak når resultatet.Mark_Type = "Mid Term Exam" deretter Resultat.merker slutt) "Midt termineksamen",
Maks (sak når resultatet.marker_type = "avsluttende eksamen" deretter resultat.merker slutt) "Avsluttende eksamen",
Sum (resultat.merker) som total
Fra resultat
Gruppe etter resultat.std_id, resultat.kurs_id
Bestill etter resultat.std_id, resultat.Course_id ASC;

Utgangen viser en ny kolonne som heter Total som viser summen av merkene til alle eksamensstyper av hvert kurs oppnådd av hver enkelt student.

Drei rader til kolonner i flere tabeller:

De to foregående spørsmålene blir brukt på resultat bord. Denne tabellen er relatert til de to andre tabellene. Disse er studenter og kurs. Hvis du vil vise studentnavnet i stedet for student -ID og kursnavn i stedet for kurs -ID, må du skrive den valgte spørringen ved å bruke tre relaterte tabeller, studenter, kurs og resultat. Følgende SELECT -spørring opprettes ved å legge til tre tabellnavn etter skjemaklausul og sette passende forhold i hvor leddet for å hente dataene fra de tre tabellene og generere mer passende utgang enn de forrige utvalgte spørsmålene.

Velg studenter.Navn som 'studentnavn', kurs.Navn som 'Kursnavn',
Maks (sak når resultatet.marker_type = "intern eksamen" deretter resultat.merker slutt) "CT",
Maks (sak når resultatet.Mark_Type = "Mid Term Exam" deretter Resultat.merker slutt) "Midt",
Maks (sak når resultatet.marker_type = "avsluttende eksamen" deretter resultat.merker slutt) "Final",
Sum (resultat.merker) som total
Fra studenter, kurs, resultat
Hvor resultatet.STD_ID = studenter.ID og resultat.kurs_id = kurs.kurs_id
Gruppe etter resultat.std_id, resultat.kurs_id
Bestill etter resultat.std_id, resultat.Course_id ASC;

Følgende utgang vil generere etter utførelse av spørringen ovenfor.

Konklusjon:

Hvordan du kan implementere funksjonaliteten til Pivot () -funksjonen uten støtte fra Pivot () -funksjonen i MySQL vises i denne artikkelen ved å bruke noen dummy -data. Jeg håper, leserne vil kunne transformere alle data på radnivå til kolonnetnivå ved å bruke SELECT-spørringen etter å ha lest denne artikkelen.