Forutsetning:
Du må opprette en database og noen relaterte tabeller der rader i en tabell vil bli konvertert til kolonnene som PIVOT () -funksjonen. Kjør følgende SQL -setninger for å lage en database med navnet ' unidb 'Og lag tre tabeller med navnet' studenter ',' kurs 'Og' resultat '. studenter og resultat tabeller vil være relatert til ett-til-mange-forhold og kurs og resultater tabeller vil være relatert til ett-til-mange-forhold her. LAG uttalelse av resultat tabellen inneholder to utenlandske nøkkelbegrensninger for feltene, std_id , og kurs_id .
LAG DATABASE unidb;
BRUK unidb;
LAG TABELLELSER(
idINT PRIMÆR NØKKEL,
navn varchar(femti)IKKE NULL,
avdeling VARCHAR(femten)IKKE NULL);
LAG TABELL -kurs(
course_id VARCHAR(tjue)PRIMÆRNØKKEL,
navn varchar(femti)IKKE NULL,
kreditt SMALLINT NOT NULL);
OPPRETT TABELL -resultat(
std_id INT NOT NULL,
course_id VARCHAR(tjue)IKKE NULL,
mark_type VARCHAR(tjue)IKKE NULL,
merker SMALLINT NOT 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 ble satt på tidspunktet for tabellopprettelsen.
SETT INN i studenter VERDIER
( '1937463','Harper Lee','CSE'),
( '1937464','Garcia Marquez','CSE'),
( '1937465','Forster, E.M.','CSE'),
( '1937466','Ralph Ellison','CSE');
SETT INN I KURSER VERDIER
( 'CSE-401','Objektorientert programmering',3),
( 'CSE-403','Data struktur',2),
( 'CSE-407','Unix -programmering',2);
SETT INN I RESULTATVERDIER
( '1937463','CSE-401','Intern eksamen',femten),
( '1937463','CSE-401','Tentamen',tjue),
( '1937463','CSE-401','Avsluttende eksamen',35),
( '1937464','CSE-403','Intern eksamen',17),
( '1937464','CSE-403','Tentamen',femten),
( '1937464','CSE-403','Avsluttende eksamen',30),
( '1937465','CSE-401','Intern eksamen',18),
( '1937465','CSE-401','Tentamen',2. 3),
( '1937465','CSE-401','Avsluttende eksamen',38),
( '1937466','CSE-407','Intern eksamen',tjue),
( '1937466','CSE-407','Tentamen',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 du konverterer disse radene til kolonner i denne tabellen for å vise dataene i et mer organisert format, vises i neste del av denne opplæringen.
Roter rader til kolonner ved å bruke CASE -setning:
Kjør følgende enkle SELECT -setning for å vise alle poster av resultat bord.
PLUKKE UT*FRA resultat;
Resultatet viser de fire studentens karakterer for tre eksamensformer på tre emner. Så verdiene til std_id , kurs_id og mark_type gjentas flere ganger for de forskjellige studentene, kursene og eksamenstypene.
Utdataene blir mer lesbare hvis SELECT -spørringen kan skrives mer effektivt ved å bruke CASE -setningen. Følgende SELECT med CASE -setningen vil transformere gjentatte verdier for radene til kolonnenavn og vise innholdet i tabellene i et mer forståelig format for brukeren.
VELG resultat.std_id, resultat.kurs_id,MAKS(SAK NÅR resultat.mark_type ='Intern eksamen'DA resultat. Merker SLUTT) 'Intern eksamen',
MAKS(SAK NÅR resultat.mark_type ='Tentamen'DA resultat. Merker SLUTT) 'Tentamen',
MAKS(SAK NÅR resultat.mark_type ='Avsluttende eksamen'DA resultat. Merker SLUTT) 'Avsluttende eksamen'
FRA resultatet
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Følgende utdata vises etter at ovenstående setning er kjørt som er mer lesbar enn den forrige utgangen.
Roter rader til kolonner med CASE og SUM ():
Hvis du vil telle det totale antallet av hvert kurs for hver elev fra tabellen, må du bruke aggregeringsfunksjonen SUM() gruppe av std_id og kurs_id med CASE -uttalelsen. Følgende spørring opprettes ved å endre den forrige spørringen med SUM () -funksjonen og GROUP BY -ledd.
VELG resultat.std_id, resultat.kurs_id,MAKS(SAK NÅR resultat.mark_type ='Intern eksamen'DA resultat. Merker SLUTT) 'Intern eksamen',
MAKS(SAK NÅR resultat.mark_type ='Tentamen'DA resultat. Merker SLUTT) 'Tentamen',
MAKS(SAK NÅR resultat.mark_type ='Avsluttende eksamen'DA resultat. Merker SLUTT) 'Avsluttende eksamen',
SUM(resultat. merker) somTotal
FRA resultatet
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Utgangen viser en ny kolonne med navnet Total som viser summen av karakterene for alle eksamentyper for hvert emne oppnådd av hver enkelt student.
Roter rader til kolonner i flere tabeller:
De to foregående spørringene brukes 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 SELECT -spørringen ved hjelp av tre relaterte tabeller, studenter , kurs og resultat . Følgende SELECT -spørring opprettes ved å legge til tre tabellnavn etter FORM -setningen og sette passende forhold i WHERE -leddet for å hente dataene fra de tre tabellene og generere mer passende utdata enn de forrige SELECT -spørringene.
VELG studentenavnsom ``Student navn``, kursnavnsom ``Kursnavn``,MAKS(SAK NÅR resultat.mark_type ='Intern eksamen'DA resultat. Merker SLUTT) 'CT',
MAKS(SAK NÅR resultat.mark_type ='Tentamen'DA resultat. Merker SLUTT) 'Midt',
MAKS(SAK NÅR resultat.mark_type ='Avsluttende eksamen'DA resultat. Merker SLUTT) 'Endelig',
SUM(resultat. merker) somTotal
FRA studenter, kurs, resultat
HVOR result.std_id = students.id og result.course_id = courses.course_id
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Følgende utdata genereres etter at spørringen ovenfor er utført.
Konklusjon:
Hvordan du kan implementere funksjonaliteten til Pivot () -funksjonen uten støtte fra Pivot () -funksjonen i MySQL er vist i denne artikkelen ved å bruke noen dummy -data. Jeg håper at leserne vil kunne transformere data på radnivå til data på kolonnivå ved å bruke SELECT-spørringen etter å ha lest denne artikkelen.