Nödvändig förutsättning:
Du måste skapa en databas och några relaterade tabeller där rader i en tabell kommer att konverteras till kolumnerna som PIVOT () -funktionen. Kör följande SQL -satser för att skapa en databas med namnet ' unidb 'Och skapa tre tabeller med namnet' studenter ',' kurser 'Och' resultat '. studenter och resultat tabeller kommer att relateras till en-till-många relation och kurser och resultat tabeller kommer att relateras till en-till-många-relation här. SKAPA uttalande av resultat tabellen innehåller två främmande nyckelbegränsningar för fälten, std_id , och kurs_id .
SKAPA DATABASE unidb;
ANVÄND unidb;
SKAPA BORDSBORD elever(
idINT PRIMÄR NYCKEL,
namn varchar(femtio)INTE NULL,
avdelningen VARCHAR(femton)INTE NULL);
SKAPA TABELLKurser(
course_id VARCHAR(tjugo)PRIMÄRNYCKEL,
namn varchar(femtio)INTE NULL,
kredit SMALLINT INTE NULL);
SKAPA TABELL -resultat(
std_id INT NOT NULL,
course_id VARCHAR(tjugo)INTE NULL,
mark_type VARCHAR(tjugo)INTE NULL,
märken SMALLINT NOT NULL,
FRÄMMANDE NYCKEL(std_id)REFERENSER studenter(id),
FRÄMMANDE NYCKEL(kurs_id)REFERENSKurser(kurs_id),
PRIMÄRNYCKEL(std_id, course_id, mark_type));
Infoga några poster i studenter, kurser och resultat bord. Värdena måste infogas i tabellerna baserat på de begränsningar som sattes vid tidpunkten för tabellskapandet.
SÄTT IN I studenter VÄRDEN
( '1937463','Harper Lee','CSE'),
( '1937464','Garcia Marquez','CSE'),
( '1937465','Forster, E.M.','CSE'),
( '1937466','Ralph Ellison','CSE');
SÄTT IN I kurser VÄRDEN
( 'CSE-401','Objektorienterad programmering',3),
( 'CSE-403','Datastruktur',2),
( 'CSE-407','Unix -programmering',2);
SÄTT IN I resultat VÄRDEN
( '1937463','CSE-401','Intern tentamen',femton),
( '1937463','CSE-401','Mellanperiodsexamen',tjugo),
( '1937463','CSE-401','Slutprov',35),
( '1937464','CSE-403','Intern tentamen',17),
( '1937464','CSE-403','Mellanperiodsexamen',femton),
( '1937464','CSE-403','Slutprov',30),
( '1937465','CSE-401','Intern tentamen',18),
( '1937465','CSE-401','Mellanperiodsexamen',2. 3),
( '1937465','CSE-401','Slutprov',38),
( '1937466','CSE-407','Intern tentamen',tjugo),
( '1937466','CSE-407','Mellanperiodsexamen',22),
( '1937466','CSE-407','Slutprov',40);
Här, resultat tabellen innehåller flera samma värden för std_id , mark_type och kurs_id kolumner i varje rad. Hur du konverterar dessa rader till kolumner i denna tabell för att visa data i ett mer organiserat format visas i nästa del av den här självstudien.
Rotera rader till kolumner med hjälp av CASE -sats:
Kör följande enkla SELECT -sats för att visa alla poster i resultat tabell.
VÄLJ*FRÅN resultat;
Utdata visar de fyra studentens betyg för tre tentamensformer av tre kurser. Så värdena på std_id , kurs_id och mark_type upprepas flera gånger för de olika studenterna, kurserna och tentamenstyperna.
Utdata blir mer läsbar om SELECT -frågan kan skrivas mer effektivt med hjälp av CASE -satsen. Följande SELECT med CASE -satsen omvandlar radernas upprepade värden till kolumnnamnen och visar innehållet i tabellerna i ett mer begripligt format för användaren.
VÄLJ resultat.std_id, result.course_id,MAX(FALL NÄR resultat.mark_typ ='Intern tentamen'Därefter resultat. Märken END) 'Intern tentamen',
MAX(FALL NÄR resultat.mark_typ ='Mellanperiodsexamen'Därefter resultat. Märken END) 'Mellanperiodsexamen',
MAX(FALL NÄR resultat.mark_typ ='Slutprov'Därefter resultat. Märken END) 'Slutprov'
FRÅN resultat
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Följande utdata visas efter att ovanstående sats har körts, vilket är mer läsbart än föregående utdata.
Rotera rader till kolumner med CASE och SUM ():
Om du vill räkna det totala antalet för varje kurs för varje elev från tabellen måste du använda aggregeringsfunktionen BELOPP() Grupp av std_id och kurs_id med CASE -uttalandet. Följande fråga skapas genom att ändra den föregående frågan med funktionen SUM () och GROUP BY -satsen.
VÄLJ resultat.std_id, result.course_id,MAX(FALL NÄR resultat.mark_typ ='Intern tentamen'Därefter resultat. Märken END) 'Intern tentamen',
MAX(FALL NÄR resultat.mark_typ ='Mellanperiodsexamen'Därefter resultat. Märken END) 'Mellanperiodsexamen',
MAX(FALL NÄR resultat.mark_typ ='Slutprov'Därefter resultat. Märken END) 'Slutprov',
BELOPP(resultat. märken) somTotal
FRÅN resultat
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Utdata visar en ny kolumn med namnet Total som visar summan av poängen för alla tentamenstyper för varje kurs som erhålls av varje enskild student.
Rotera rader till kolumner i flera tabeller:
De två föregående frågorna tillämpas på resultat tabell. Denna tabell är relaterad till de andra två tabellerna. Dessa är studenter och kurser . Om du vill visa studentnamnet istället för student -id och kursnamn istället för kurs -id måste du skriva SELECT -frågan med hjälp av tre relaterade tabeller, studenter , kurser och resultat . Följande SELECT -fråga skapas genom att lägga till tre tabellnamn efter FORM -satsen och ställa in lämpliga villkor i WHERE -satsen för att hämta data från de tre tabellerna och generera mer lämplig output än de tidigare SELECT -frågorna.
VÄLJ elevernas namnsom ''Elevs namn'', kurser.namnsom ''Kursnamn'',MAX(FALL NÄR resultat.mark_typ ='Intern tentamen'Därefter resultat. Märken END) 'CT',
MAX(FALL NÄR resultat.mark_typ ='Mellanperiodsexamen'Därefter resultat. Märken END) 'Mitten',
MAX(FALL NÄR resultat.mark_typ ='Slutprov'Därefter resultat. Märken END) 'Slutlig',
BELOPP(resultat. märken) somTotal
FRÅN studenter, kurser, resultat
VAR result.std_id = students.id och result.course_id = courses.course_id
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Följande utdata genereras efter att ovanstående fråga har körts.
Slutsats:
Hur du kan implementera funktionen för Pivot () -funktionen utan stöd av Pivot () -funktionen i MySQL visas i den här artikeln med hjälp av några dummy -data. Jag hoppas att läsarna kommer att kunna omvandla data på radnivå till data på kolumnnivå med hjälp av SELECT-frågan efter att ha läst den här artikeln.