Pivot mysql Rolve rotanti alle colonne

Una tabella di database può archiviare diversi tipi di dati e talvolta dobbiamo trasformare i dati a livello di riga in dati a livello di colonna. Questo problema può essere risolto utilizzando la funzione pivot (). Questa funzione viene utilizzata per ruotare le righe di una tabella in valori di colonna. Ma questa funzione è supportata da pochissimi server di database, come Oracle o SQL Server. Se si desidera eseguire la stessa attività nella tabella del database MySQL, è necessario scrivere la query Seleziona usando l'istruzione Caso per ruotare le righe nelle colonne. L'articolo mostra il modo di eseguire la funzione Task of Pivot () all'interno delle tabelle di database MySQL correlate.

Prerequisito:

È necessario creare un database e alcune tabelle correlate in cui le righe di una tabella verranno convertite in colonne come pivot (). Esegui le seguenti istruzioni SQL per creare un database denominato 'unidb'e crea tre tabelle denominate'studenti","corsi' E 'risultato'. studenti E risultato Le tabelle saranno correlate dalla relazione one-to-many e corsi E Risultati Le tabelle saranno correlate dalla relazione one-to-many qui. Creare una dichiarazione di risultato La tabella contiene due vincoli chiave estere per i campi, std_id, E Corso_id.

Creare database unidb;
Usare unidb;
Crea studenti da tavolo (
Id Int Key primario,
Nome varchar (50) non null,
dipartimento varchar (15) non null);
Crea corsi di tabelle (
Course_id Varchar (20) Chiave primaria,
Nome varchar (50) non null,
Credito Smallint non null);
Crea il risultato della tabella (
std_id int non null,
Course_id varchar (20) non null,
mark_type varchar (20) non null,
segna piccoli non null,
Foreign Key (STD_ID) Riferimenti Studenti (ID),
Corsi di riferimenti a chiave straniera (Course_id) (Course_ID),
Chiave primaria (std_id, course_id, mark_type));

Inserire alcuni record in Studenti, corsi e risultati tavoli. I valori devono essere inseriti nelle tabelle in base alle restrizioni impostate al momento della creazione della tabella.

Inserire nei valori degli studenti
("1937463", "Harper Lee", "CSE"),
("1937464", "Garcia Marquez", "CSE"),
("1937465", "Forster, E.M.',' CSE '),
("1937466", "Ralph Ellison", "CSE");
Inserire i valori dei corsi
('CSE-401', 'Programmazione orientata agli oggetti', 3),
("CSE-403", "Struttura dei dati", 2),
("CSE-407", "programmazione unix", 2);
Inserire nei valori dei risultati
("1937463", "CSE-401", "esame interno", 15),
("1937463", "CSE-401", "Mid Term Exam", 20),
("1937463", "CSE-401", "esame finale", 35),
("1937464", "CSE-403", "esame interno", 17),
("1937464", "CSE-403", "Mid Term Exam", 15),
("1937464", "CSE-403", "esame finale", 30),
("1937465", "CSE-401", "esame interno", 18),
("1937465", "CSE-401", "Mid Term Exam", 23),
("1937465", "CSE-401", "esame finale", 38),
("1937466", "CSE-407", "esame interno", 20),
("1937466", "CSE-407", "Mid Term Exam", 22),
("1937466", "CSE-407", "esame finale", 40);

Qui, risultato La tabella contiene più stessi valori per std_id, mark_type E Corso_id colonne in ogni riga. Come convertire queste righe in colonne di questa tabella per la visualizzazione dei dati in un formato più organizzato è mostrato nella parte successiva di questo tutorial.

Ruota le righe alle colonne usando l'istruzione Case:

Esegui la seguente semplice istruzione Select per visualizzare tutti i record del risultato tavolo.

Seleziona * dal risultato;

L'output mostra i marchi dei quattro studenti per tre tipi di esame di tre corsi. Quindi i valori di std_id, Corso_id E mark_type vengono ripetuti più volte per i diversi studenti, corsi e tipi di esame.

L'output sarà più leggibile se la query selezionata può essere scritta in modo più efficiente utilizzando l'istruzione Case. Quanto segue Selezionare con l'istruzione Case trasformerà i valori ripetuti delle righe nei nomi delle colonne e visualizzerà il contenuto delle tabelle in un formato più comprensibile per l'utente.

Seleziona il risultato.std_id, risultato.Corso_id,
Max (caso in cui il risultato.mark_type = "esame interno" quindi risultato.segni fine) "esame interno",
Max (caso in cui il risultato.mark_type = "esame medio termine" quindi risultato.End marks) "esame di medio termine",
Max (caso in cui il risultato.mark_type = "esame finale" quindi risultato.End marks) "esame finale"
Da risultato
Gruppo per risultato.std_id, risultato.Corso_id
Ordine per risultato.std_id, risultato.Course_id ASC;

La seguente output apparirà dopo aver eseguito l'istruzione sopra che è più leggibile dell'output precedente.

Ruota le righe alle colonne usando Case and Sum ():

Se si desidera contare il numero totale di ogni corso di ogni studente dalla tabella, è necessario utilizzare la funzione aggregata SOMMA() raggruppa per std_id E Corso_id Con la dichiarazione del caso. La seguente query viene creata modificando la funzione precedente con Sum () e gruppo per clausola.

Seleziona il risultato.std_id, risultato.Corso_id,
Max (caso in cui il risultato.mark_type = "esame interno" quindi risultato.segni fine) "esame interno",
Max (caso in cui il risultato.mark_type = "esame medio termine" quindi risultato.End marks) "esame di medio termine",
Max (caso in cui il risultato.mark_type = "esame finale" quindi risultato.End marks) "esame finale",
Somma (risultato.segni) per il totale
Da risultato
Gruppo per risultato.std_id, risultato.Corso_id
Ordine per risultato.std_id, risultato.Course_id ASC;

L'output mostra una nuova colonna denominata Totale che mostra la somma dei segni di tutti i tipi di esame di ciascun corso ottenuto da ogni particolare studente.

Ruotare le righe alle colonne in più tabelle:

Le due domande precedenti vengono applicate al risultato tavolo. Questa tabella è correlata alle altre due tabelle. Questi sono studenti E corsi. Se si desidera visualizzare il nome dello studente anziché l'ID studente e il nome del corso anziché l'ID del corso, è necessario scrivere la query selezionati utilizzando tre tabelle correlate, studenti, corsi E risultato. La seguente query selezionata viene creata aggiungendo tre nomi di tabella dopo la clausola del modulo e impostando le condizioni appropriate nella clausola WHE per recuperare i dati dalle tre tabelle e generare output più appropriato rispetto alle precedenti query selezionate.

Seleziona gli studenti.Nome come "nome studente", corsi.Nome come "nome del corso",
Max (caso in cui il risultato.mark_type = "esame interno" quindi risultato.segni fine) "ct",
Max (caso in cui il risultato.mark_type = "esame medio termine" quindi risultato.segni fine) "mid",
Max (caso in cui il risultato.mark_type = "esame finale" quindi risultato.segni fine) "finale",
Somma (risultato.segni) per il totale
Da studenti, corsi, risultato
Dove risultato.std_id = studenti.id e risultato.Course_id = corsi.Corso_id
Gruppo per risultato.std_id, risultato.Corso_id
Ordine per risultato.std_id, risultato.Course_id ASC;

Il seguente output genererà dopo aver eseguito la query sopra.

Conclusione:

Come è possibile implementare la funzionalità della funzione Pivot () senza il supporto della funzione Pivot () in MySQL è mostrato in questo articolo utilizzando alcuni dati fittizi. Spero che i lettori saranno in grado di trasformare qualsiasi dati a livello di riga in dati a livello di colonna utilizzando la query selezionata dopo aver letto questo articolo.