Pivot dans SQLite
Je voudrais obtenir un tableau qui montre les étudiants et les notes qu'ils reçoivent pour tous leurs sujets dans une requête.
C'est ma structure de table:
Tableau: markdetails
## studid ## ## subjectid ## ## marks ##
A1 3 50
A1 4 60
A1 5 70
B1 3 60
B1 4 80
C1 5 95
Tableau: student info
Structure Réelle:
## studid ## ## name ##
A1 Raam
B1 Vivek
c1 Alex
Je veux que le jeu de résultats ressemble à ceci:
Tableau: Student Info
## studid ## ## name## ## subjectid_3 ## ## subjectid_4 ## ## subjectid_5 ##
A1 Raam 50 60 70
B1 Vivek 60 80 null
c1 Alex null null 95
Comment puis-je accomplir cela dans SQLite?
4 réponses
Vous devez d'abord changer la table actuelle en une table temporaire:
alter table student_info rename to student_name
Ensuite, vous aurez envie de recréer student_info:
create table student_info add column (
stuid VARCHAR(5) PRIMARY KEY,
name VARCHAR(255),
subjectid_3 INTEGER,
subjectid_4 INTEGER,
subjectid_5 INTEGER
)
Ensuite, remplissez student_info:
insert into student_info
select
u.stuid,
u.name,
s3.marks as subjectid_3,
s4.marks as subjectid_4,
s5.marks as subjectid_5
from
student_temp u
left outer join markdetails s3 on
u.stuid = s3.stuid
and s3.subjectid = 3
left outer join markdetails s4 on
u.stuid = s4.stuid
and s4.subjectid = 4
left outer join markdetails s5 on
u.stuid = s5.stuid
and s5.subjectid = 5
Maintenant, déposez simplement votre table temporaire:
drop table student_temp
Et c'est ainsi que vous pouvez rapidement mettre à jour votre table.
SQLite manque d'une fonction pivot, donc le mieux que vous pouvez faire est de coder en dur certaines jointures de gauche. A left join fera correspondre toutes les lignes dans ses conditions de jointure et retournera null pour toutes les lignes de la première table ou de la gauche cela ne répond pas aux conditions de jointure pour la deuxième table.
Puisque l'auteur n'a pas eu la gentillesse de donner le SQL pour créer le schéma, le voici pour tous ceux qui veulent essayer la solution de @Eric.
create table markdetails (studid, subjectid, marks);
create table student_info (studid, name);
insert into markdetails values('A1', 3, 50);
insert into markdetails values('A1', 4, 60);
insert into markdetails values('A1', 5, 70);
insert into markdetails values('B1', 3, 60);
insert into markdetails values('B1', 4, 80);
insert into markdetails values('C1', 5, 95);
insert into student_info values('A1', 'Raam');
insert into student_info values('B1', 'Vivek');
insert into student_info values('C1', 'Alex');
Voici une solution alternative utilisant case avec group by.
select
si.studid,
si.name,
sum(case when md.subjectid = 3 then md.marks end) subjectid_3,
sum(case when md.subjectid = 4 then md.marks end) subjectid_4,
sum(case when md.subjectid = 5 then md.marks end) subjectid_5
from student_info si
join markdetails md on
md.studid = si.studid
group by si.studid, si.name
;
À titre de comparaison, voici la même instruction select de la solution de @ Eric:
select
u.stuid,
u.name,
s3.marks as subjectid_3,
s4.marks as subjectid_4,
s5.marks as subjectid_5
from
student_temp u
left outer join markdetails s3 on
u.stuid = s3.stuid
and s3.subjectid = 3
left outer join markdetails s4 on
u.stuid = s4.stuid
and s4.subjectid = 4
left outer join markdetails s5 on
u.stuid = s5.stuid
and s5.subjectid = 5
;
Il sera intéressant de voir lequel fonctionnerait le mieux quand il y a beaucoup de données.
Grande annexe! m'a aidé à résoudre un problème similaire avec un faible effort et une charge système. J'utilise un Raspberry Pi pour obtenir des données de capteur de température DS18B20 1wire-interface comme suit:
CREATE TABLE temps (Timestamp DATETIME, sensorID TEXT, temperature NUMERIC);
Exemple:
sqlite> .headers on
sqlite> .mode column
sqlite> select * from temps where timestamp > '2014-02-24 22:00:00';
Timestamp sensorID temperature
------------------- --------------- -----------
2014-02-24 22:00:02 28-0000055f3f10 19.937
2014-02-24 22:00:03 28-0000055f0378 19.687
2014-02-24 22:00:04 28-0000055eb504 19.937
2014-02-24 22:00:05 28-0000055f92f2 19.937
2014-02-24 22:00:06 28-0000055eef29 19.812
2014-02-24 22:00:07 28-0000055f7619 19.625
2014-02-24 22:00:08 28-0000055edf01 19.687
2014-02-24 22:00:09 28-0000055effda 19.812
2014-02-24 22:00:09 28-0000055e5ef2 19.875
2014-02-24 22:00:10 28-0000055f1b83 19.812
2014-02-24 22:10:03 28-0000055f3f10 19.937
2014-02-24 22:10:04 28-0000055f0378 19.75
2014-02-24 22:10:04 28-0000055eb504 19.937
2014-02-24 22:10:05 28-0000055f92f2 19.937
En utilisant la commande SUBSTR (), Je "normalise" les horodatages à des périodes de 10 minutes. Avec JOIN, le sensorID est changé en SensorName à l'aide de la table de recherche 'sensors'
CREATE VIEW [TempsSlot10min] AS
SELECT SUBSTR(datetime(timestamp),1,15)||'0:00' AS TimeSlot,
SensorName,
temperature FROM
temps JOIN sensors USING (sensorID, sensorID);
Exemple:
sqlite> select * from TempsSlot10min where timeslot >= '2014-02-24 22:00:00';
TimeSlot SensorName temperature
------------------- ---------- -----------
2014-02-24 22:00:00 T1 19.937
2014-02-24 22:00:00 T2 19.687
2014-02-24 22:00:00 T3 19.937
2014-02-24 22:00:00 T4 19.937
2014-02-24 22:00:00 T5 19.812
2014-02-24 22:00:00 T6 19.625
2014-02-24 22:00:00 T10 19.687
2014-02-24 22:00:00 T9 19.812
2014-02-24 22:00:00 T8 19.875
2014-02-24 22:00:00 T7 19.812
2014-02-24 22:10:00 T1 19.937
2014-02-24 22:10:00 T2 19.75
2014-02-24 22:10:00 T3 19.937
2014-02-24 22:10:00 T4 19.937
2014-02-24 22:10:00 T5 19.875
Maintenant, la magie se produit avec ce qui précède CAS de l'instruction.
CREATE VIEW [PivotTemps10min] AS
SELECT TimeSlot,
AVG(CASE WHEN sensorName = 'T1' THEN temperature END) AS T1,
AVG(CASE WHEN sensorName = 'T2' THEN temperature END) AS T2,
...
AVG(CASE WHEN sensorName = 'T10' THEN temperature END) AS T10
FROM TempsSlot10min
GROUP BY TimeSlot;
Exemple:
select * from PivotTemps10min where timeslot >= '2014-02-24 22:00:00';
TimeSlot T1 T2 T10
------------------- ---------- ---------- ... ----------
2014-02-24 22:00:00 19.937 19.687 19.687
2014-02-24 22:10:00 19.937 19.75 19.687
2014-02-24 22:20:00 19.937 19.75 19.687
2014-02-24 22:30:00 20.125 19.937 19.937
2014-02-24 22:40:00 20.187 20.0 19.937
2014-02-24 22:50:00 20.25 20.062 20.062
2014-02-24 23:00:00 20.25 20.062 20.062
Le seul problème qui reste ici est que le sensorName 'T1'... 'T10' est maintenant codé en dur dans la vue [PivotTemps10min] et ne provient pas de la table de recherche.
Néanmoins, je vous remercie beaucoup pour les réponses dans cette thead!
Si vous avez une exigence plus simple de regrouper les enfants dans le même champ, group_concat est votre ami.
Un grand merci à Simon Slaver de ce fil: http://sqlite.1065341.n5.nabble.com/Howto-pivot-in-SQLite-tp26766p26771.html