Forudsætning:
Du skal oprette en database og nogle relaterede tabeller, hvor rækker i en tabel vil blive konverteret til kolonnerne som PIVOT () -funktion. Kør følgende SQL -sætninger for at oprette en database med navnet ' unidb 'Og opret tre tabeller med navnet' studerende ',' kurser 'Og' resultat '. studerende og resultat tabeller vil blive relateret af en-til-mange relation og kurser og resultater tabeller vil være relateret til et-til-mange-forhold her. Opret erklæring fra resultat tabellen indeholder to udenlandske nøglebegrænsninger for felterne, std_id , og kursus_id .
Opret DATABASE unidb;
BRUG unidb;
Opret tabel -elever(
idINT PRIMÆR NØGLE,
navn varchar(halvtreds)IKKE NULL,
afdeling VARCHAR(femten)IKKE NULL);
Opret tabel -kurser(
kursus_id VARCHAR(tyve)PRIMÆRNØGLE,
navn varchar(halvtreds)IKKE NULL,
kredit SMALLINT IKKE NULL);
OPRET TABEL -resultat(
std_id INT IKKE NULL,
kursus_id VARCHAR(tyve)IKKE NULL,
mark_type VARCHAR(tyve)IKKE NULL,
mærker SMALLINT NOT NULL,
FREMMED NØGLE(std_id)REFERENCER studerende(id),
FREMMED NØGLE(kursus_id)REFERENCER kurser(kursus_id),
PRIMÆRNØGLE(std_id, course_id, mark_type));
Indsæt nogle poster i studerende, kurser og resultat borde. Værdierne skal indsættes i tabellerne baseret på de begrænsninger, der blev fastsat på tidspunktet for bordoprettelsen.
SÆT IN I elevernes VÆRDIER
( '1937463','Harper Lee','CSE'),
( '1937464','Garcia Marquez','CSE'),
( '1937465','Forster, E.M.','CSE'),
( '1937466','Ralph Ellison','CSE');
SÆT IN I kurser VÆRDIER
( 'CSE-401','Objektorienteret programmering',3),
( 'CSE-403','Datastruktur',2),
( 'CSE-407','Unix programmering',2);
INDSÆT I RESULTATVÆRDIER
( '1937463','CSE-401','Intern eksamen',femten),
( '1937463','CSE-401','Midtårseksamen',tyve),
( '1937463','CSE-401','Afsluttende eksamen',35),
( '1937464','CSE-403','Intern eksamen',17),
( '1937464','CSE-403','Midtårseksamen',femten),
( '1937464','CSE-403','Afsluttende eksamen',30),
( '1937465','CSE-401','Intern eksamen',18),
( '1937465','CSE-401','Midtårseksamen',2. 3),
( '1937465','CSE-401','Afsluttende eksamen',38),
( '1937466','CSE-407','Intern eksamen',tyve),
( '1937466','CSE-407','Midtårseksamen',22),
( '1937466','CSE-407','Afsluttende eksamen',40);
Her, resultat tabellen indeholder flere samme værdier for std_id , mark_type og kursus_id kolonner i hver række. Sådan konverteres disse rækker til kolonner i denne tabel til visning af dataene i et mere organiseret format, vises i den næste del af denne vejledning.
Drej rækker til kolonner ved hjælp af CASE -sætning:
Kør følgende enkle SELECT -sætning for at vise alle registreringer af resultat bord.
VÆLG*FRA resultat;
Output viser de fire studerendes karakterer for tre eksamensformer på tre kurser. Så værdierne af std_id , kursus_id og mark_type gentages flere gange for de forskellige studerende, kurser og eksamenstyper.
Outputtet bliver mere læseligt, hvis SELECT -forespørgslen kan skrives mere effektivt ved hjælp af CASE -sætningen. Følgende SELECT med CASE -sætningen vil omdanne de gentagne værdier af rækkerne til kolonnenavne og vise tabellernes indhold i et mere forståeligt format for brugeren.
VÆLG result.std_id, result.course_id,MAX(SAG NÅR resultat.mark_type ='Intern eksamen'SÅ resultat. Mærker END) 'Intern eksamen',
MAX(SAG NÅR resultat.mark_type ='Midtårseksamen'SÅ resultat. Mærker END) 'Midtårseksamen',
MAX(SAG NÅR resultat.mark_type ='Afsluttende eksamen'SÅ resultat. Mærker END) 'Afsluttende eksamen'
FRA resultat
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Det følgende output vises efter at ovenstående sætning er kørt, hvilket er mere læsbart end det tidligere output.
Drej rækker til kolonner ved hjælp af CASE og SUM ():
Hvis du vil tælle det samlede antal af hvert kursus for hver elev fra tabellen, skal du bruge den samlede funktion SUM() gruppe efter std_id og kursus_id med CASE -erklæringen. Den følgende forespørgsel oprettes ved at ændre den forrige forespørgsel med funktionen SUM () og GROUP BY -klausulen.
VÆLG result.std_id, result.course_id,MAX(SAG NÅR resultat.mark_type ='Intern eksamen'SÅ resultat. Mærker END) 'Intern eksamen',
MAX(SAG NÅR resultat.mark_type ='Midtårseksamen'SÅ resultat. Mærker END) 'Midtårseksamen',
MAX(SAG NÅR resultat.mark_type ='Afsluttende eksamen'SÅ resultat. Mærker END) 'Afsluttende eksamen',
SUM(resultat. mærker) somi alt
FRA resultat
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Outputtet viser en ny kolonne med navnet i alt der viser summen af karaktererne for alle eksamensformer for hvert kursus opnået af hver enkelt elev.
Drej rækker til kolonner i flere tabeller:
De to foregående forespørgsler anvendes på resultat bord. Denne tabel er relateret til de to andre tabeller. Disse er studerende og kurser . Hvis du vil vise elevnavnet i stedet for elev -id og kursusnavn i stedet for kursus -id, skal du skrive SELECT -forespørgslen ved hjælp af tre relaterede tabeller, studerende , kurser og resultat . Den følgende SELECT -forespørgsel oprettes ved at tilføje tre tabelnavne efter FORM -klausulen og angive passende betingelser i WHERE -klausulen for at hente dataene fra de tre tabeller og generere mere passende output end de tidligere SELECT -forespørgsler.
VÆLG elevernes navnsom ``Elevnavn``, kurser. navnsom ``Kursusnavn``,MAX(SAG NÅR resultat.mark_type ='Intern eksamen'SÅ resultat. Mærker END) 'CT',
MAX(SAG NÅR resultat.mark_type ='Midtårseksamen'SÅ resultat. Mærker END) 'Midt',
MAX(SAG NÅR resultat.mark_type ='Afsluttende eksamen'SÅ resultat. Mærker END) 'Endelig',
SUM(resultat. mærker) somi alt
FRA studerende, kurser, 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 output genereres efter udførelsen af ovenstående forespørgsel.
Konklusion:
Hvordan du kan implementere funktionaliteten af Pivot () -funktionen uden understøttelse af Pivot () -funktionen i MySQL er vist i denne artikel ved hjælp af nogle dummy -data. Jeg håber, at læserne vil være i stand til at omdanne alle data på rækkeniveau til data på kolonneniveau ved at bruge SELECT-forespørgslen efter at have læst denne artikel.