Pivot Mysql sur tables dynamiques

Bonjour,

J'ai actuellement une requête qui me sort les éléments de cette manière :

Code:

---

1 2 3 4 5 6 7 8

id date category subcategory value 001 date1 cat1 sub1 X 002 date1 cat1 sub2 X 003 date1 cat1 sub3 X 004 date1 cat2 sub1 X 005 date1 cat3 sub1 X 006 date1 cat3 sub2 X

---

J'aimerai avoir ce résultat :

Code:

---

1 2 3

date cat1_sub1 cat1_sub2 cat1_sub3 cat2_sub1 cat3_sub1 cat3_sub2 date1 X X X X X X

---

Voici un exemple de ma requête :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT DATE_FORMAT(FROM_UNIXTIME(data.timestamp), '%d %m %Y %h:%i:%s') as timestamp, data_sub_category.designation as sub, data_key.designation as dkey, data_row.value as value FROM data, data_row, data_sub_category, data_key, data_category WHERE data.id = data_row.id_data AND data_category.id = data_row.id_data_category AND data_sub_category.id = data_row.id_data_sub_category AND data_key.id = data_row.id_data_key AND data_row.id_data_sub_category IN (1,2,3,22,1,2,3,22) AND data_row.id_data_key IN (3,4,5,6,9,10,11,12,15,16,17,18,92,93,94,95,96,97,98,99,100,101,3,4,5,6,9,10,11,12,15,16,17,18,92,93,94,95,96,97,98,99,100,101) AND data.timestamp BETWEEN 1536271200 AND 1536357540 ORDER BY data.timestamp

---

On m'a recommandé ce lien sur Stackoverflow, cependant le problème avec ma requête, c'est que je ne connais pas d'avance le nom de colonnes.

Je ne vois pas comment adapter ma requête pour mettre en place le pivot.

En vous remerciant pour votre aide.
Valentin

Puisque la structure des tables est dynamique, l'écriture de la requête doit l'être aussi. Pas d'autre moyen que de passer par du SQL procédural pour contruire la requête à la volée en fonction du contenu des tables.

Salut valdu02100.

En regardant de plus votre requête, il y a un tas de choses qui ne vont pas :

1) ne nommez pas vos colonnes en utilisant des mots réservés de MySql
--> value
--> timestamp

2) cela fait depuis plus de 20 ans que les jointures existent, pourquoi ne pas les utiliser ?

3) vous utilisez le format unix pour la colonne "timestamp". On ne procède pas ainsi dans MySql.
Vous devez définir un type correcte, par exemple "datetime".

Puis pour vos tests, vous devez utiliser le format "YYYY-MM-DD HH:MM:SS".
Et comme je l'ai dit ci-dessus, on ne nomme pas une colonne par un mot réservée.
Ce qui donne pour la déclaration de votre colonne :

Code:

---

`periode` DATETIME NOT NULL,

---

et dans le select, vous mettez :

Code:

---

1 2 3

select `periode`, ... AND data.periode BETWEEN '2018-09-06 22:00:00' AND '2018-09-01 21:59:00'

---

Au moins, cette requête est lisible.

4) est-ce utile de tester une liste de valeur aussi longue que celle-ci : "3,4,5,6,9,10,11,12,15,16,17,18,92,93,94,95,96,97,98,99,100,101,3,4,5,6,9,10,11,12,15,16,17,18,92,93,94,95,96,97,98,99,100,101" ?
Ne serait-il pas plus judicieux de créer une table de categorie, ou je ne sais quoi d'autre et de les insérer dans si ce sont toujours les mêmes valeurs ?

Citation:

---

Envoyé par valdu02100

le problème avec ma requête, c'est que je ne connais pas d'avance le nom de colonnes.

---

5) ce que vous demandez est un traitement de présentation.
Un SGBD comme mysql n'a pas vocation à faire de la présentation mais plutôt à extraire selon des critères vos données.
Vous devez le faire en php !

Vous avez aussi un autre problème, celui du nombre variable de colonne (category+subcategory).
En php, comment allez-vous définir votre requête, puisque vous ne connaissez ni le nom des colonnes, ni le nombre de colonnes que vous aurez ?

@+

Salut valdu02100.

Voici un exemple où le nombre des colonnes est fixe et leur nom est connu à l'avance.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236

-------------- SET AUTOCOMMIT = 0 --------------   -------------- START TRANSACTION --------------   -------------- DROP DATABASE IF EXISTS `base` --------------   -------------- CREATE DATABASE `base` DEFAULT CHARACTER SET `latin1` DEFAULT COLLATE `latin1_general_ci` --------------   -------------- DROP TABLE IF EXISTS `mystat` --------------   -------------- CREATE TABLE `mystat` ( `client` integer unsigned NOT NULL, `nom_client` varchar(255) NOT NULL, `annee` smallint unsigned NOT NULL, `mois` smallint unsigned NOT NULL, `montant` decimal(15,2) NOT NULL, primary key (`client`,`annee`,`mois`) ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- insert into `mystat` (`client`,`nom_client`,`annee`,`mois`,`montant`) values (125, 'dupond', 2016, 1, 100.00), (125, 'dupond', 2016, 2, 105.00), (125, 'dupond', 2016, 3, 110.00), (125, 'dupond', 2016, 4, 115.00), (125, 'dupond', 2016, 5, 120.00), (125, 'dupond', 2016, 6, 125.00), (125, 'dupond', 2016, 7, 130.00), (125, 'dupond', 2016, 8, 135.00), (125, 'dupond', 2016, 9, 140.00), (125, 'dupond', 2016, 10, 145.00), (125, 'dupond', 2016, 11, 150.00), (125, 'dupond', 2016, 12, 155.00), (217, 'durand', 2016, 1, 20.00), (217, 'durand', 2016, 2, 25.00), (217, 'durand', 2016, 3, 30.00), (217, 'durand', 2016, 4, 35.00), (217, 'durand', 2016, 5, 40.00), (217, 'durand', 2016, 6, 45.00), (217, 'durand', 2016, 7, 50.00), (217, 'durand', 2016, 8, 55.00), (217, 'durand', 2016, 9, 60.00), (217, 'durand', 2016, 10, 65.00), (217, 'durand', 2016, 11, 70.00), (217, 'durand', 2016, 12, 80.00) --------------   -------------- select * from mystat --------------   +--------+------------+-------+------+---------+ | client | nom_client | annee | mois | montant | +--------+------------+-------+------+---------+ | 125 | dupond | 2016 | 1 | 100.00 | | 125 | dupond | 2016 | 2 | 105.00 | | 125 | dupond | 2016 | 3 | 110.00 | | 125 | dupond | 2016 | 4 | 115.00 | | 125 | dupond | 2016 | 5 | 120.00 | | 125 | dupond | 2016 | 6 | 125.00 | | 125 | dupond | 2016 | 7 | 130.00 | | 125 | dupond | 2016 | 8 | 135.00 | | 125 | dupond | 2016 | 9 | 140.00 | | 125 | dupond | 2016 | 10 | 145.00 | | 125 | dupond | 2016 | 11 | 150.00 | | 125 | dupond | 2016 | 12 | 155.00 | | 217 | durand | 2016 | 1 | 20.00 | | 217 | durand | 2016 | 2 | 25.00 | | 217 | durand | 2016 | 3 | 30.00 | | 217 | durand | 2016 | 4 | 35.00 | | 217 | durand | 2016 | 5 | 40.00 | | 217 | durand | 2016 | 6 | 45.00 | | 217 | durand | 2016 | 7 | 50.00 | | 217 | durand | 2016 | 8 | 55.00 | | 217 | durand | 2016 | 9 | 60.00 | | 217 | durand | 2016 | 10 | 65.00 | | 217 | durand | 2016 | 11 | 70.00 | | 217 | durand | 2016 | 12 | 80.00 | +--------+------------+-------+------+---------+ -------------- DROP TABLE IF EXISTS `sample` --------------   -------------- CREATE TABLE `sample` as select client, annee, sum(montant) as montant, 'a' as periode from mystat group by client, annee, periode union select client, annee, sum(montant) as montant, concat('t', quarter(concat(annee, '-', mois, '-01'))) as periode from mystat group by client, annee, periode union select client, annee, sum(montant) as montant, concat('m', mois) as periode from mystat group by client, annee, periode order by client, annee, periode --------------   -------------- select * from sample --------------   +--------+-------+---------+---------+ | client | annee | montant | periode | +--------+-------+---------+---------+ | 125 | 2016 | 1530.00 | a | | 125 | 2016 | 100.00 | m1 | | 125 | 2016 | 145.00 | m10 | | 125 | 2016 | 150.00 | m11 | | 125 | 2016 | 155.00 | m12 | | 125 | 2016 | 105.00 | m2 | | 125 | 2016 | 110.00 | m3 | | 125 | 2016 | 115.00 | m4 | | 125 | 2016 | 120.00 | m5 | | 125 | 2016 | 125.00 | m6 | | 125 | 2016 | 130.00 | m7 | | 125 | 2016 | 135.00 | m8 | | 125 | 2016 | 140.00 | m9 | | 125 | 2016 | 315.00 | t1 | | 125 | 2016 | 360.00 | t2 | | 125 | 2016 | 405.00 | t3 | | 125 | 2016 | 450.00 | t4 | | 217 | 2016 | 575.00 | a | | 217 | 2016 | 20.00 | m1 | | 217 | 2016 | 65.00 | m10 | | 217 | 2016 | 70.00 | m11 | | 217 | 2016 | 80.00 | m12 | | 217 | 2016 | 25.00 | m2 | | 217 | 2016 | 30.00 | m3 | | 217 | 2016 | 35.00 | m4 | | 217 | 2016 | 40.00 | m5 | | 217 | 2016 | 45.00 | m6 | | 217 | 2016 | 50.00 | m7 | | 217 | 2016 | 55.00 | m8 | | 217 | 2016 | 60.00 | m9 | | 217 | 2016 | 75.00 | t1 | | 217 | 2016 | 120.00 | t2 | | 217 | 2016 | 165.00 | t3 | | 217 | 2016 | 215.00 | t4 | +--------+-------+---------+---------+ -------------- drop view if exists `vue` --------------   -------------- create view `vue` as select client, annee, case periode when 'a' then montant else 0 end as an, case periode when 't1' then montant else 0 end as trim_1, case periode when 't2' then montant else 0 end as trim_2, case periode when 't3' then montant else 0 end as trim_3, case periode when 't4' then montant else 0 end as trim_4, case periode when 'm1' then montant else 0 end as mois_01, case periode when 'm2' then montant else 0 end as mois_02, case periode when 'm3' then montant else 0 end as mois_03, case periode when 'm4' then montant else 0 end as mois_04, case periode when 'm5' then montant else 0 end as mois_05, case periode when 'm6' then montant else 0 end as mois_06, case periode when 'm7' then montant else 0 end as mois_07, case periode when 'm8' then montant else 0 end as mois_08, case periode when 'm9' then montant else 0 end as mois_09, case periode when 'm10' then montant else 0 end as mois_10, case periode when 'm11' then montant else 0 end as mois_11, case periode when 'm12' then montant else 0 end as mois_12 from sample --------------   -------------- select * from vue --------------   +--------+-------+---------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+ | client | annee | an | trim_1 | trim_2 | trim_3 | trim_4 | mois_01 | mois_02 | mois_03 | mois_04 | mois_05 | mois_06 | mois_07 | mois_08 | mois_09 | mois_10 | mois_11 | mois_12 | +--------+-------+---------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+ | 125 | 2016 | 1530.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 125 | 2016 | 0 | 0 | 0 | 0 | 0 | 100.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 125 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 145.00 | 0 | 0 | | 125 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 150.00 | 0 | | 125 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 155.00 | | 125 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 105.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 125 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 110.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 125 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 115.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 125 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 120.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 125 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 125.00 | 0 | 0 | 0 | 0 | 0 | 0 | | 125 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 130.00 | 0 | 0 | 0 | 0 | 0 | | 125 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 135.00 | 0 | 0 | 0 | 0 | | 125 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 140.00 | 0 | 0 | 0 | | 125 | 2016 | 0 | 315.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 125 | 2016 | 0 | 0 | 360.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 125 | 2016 | 0 | 0 | 0 | 405.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 125 | 2016 | 0 | 0 | 0 | 0 | 450.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 217 | 2016 | 575.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 217 | 2016 | 0 | 0 | 0 | 0 | 0 | 20.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 217 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 65.00 | 0 | 0 | | 217 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 70.00 | 0 | | 217 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 80.00 | | 217 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 25.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 217 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 30.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 217 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 35.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 217 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 40.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 217 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 45.00 | 0 | 0 | 0 | 0 | 0 | 0 | | 217 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 50.00 | 0 | 0 | 0 | 0 | 0 | | 217 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 55.00 | 0 | 0 | 0 | 0 | | 217 | 2016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 60.00 | 0 | 0 | 0 | | 217 | 2016 | 0 | 75.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 217 | 2016 | 0 | 0 | 120.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 217 | 2016 | 0 | 0 | 0 | 165.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 217 | 2016 | 0 | 0 | 0 | 0 | 215.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +--------+-------+---------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+ -------------- select client, annee, max(an) as an, max(trim_1) as trim_1, max(trim_2) as trim_2, max(trim_3) as trim_3, max(trim_4) as trim_4, max(mois_01) as mois_01, max(mois_02) as mois_02, max(mois_03) as mois_03, max(mois_04) as mois_04, max(mois_05) as mois_05, max(mois_06) as mois_06, max(mois_07) as mois_07, max(mois_08) as mois_08, max(mois_09) as mois_09, max(mois_10) as mois_10, max(mois_11) as mois_11, max(mois_12) as mois_12 from vue   group by client, annee order by client, annee --------------   +--------+-------+---------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+ | client | annee | an | trim_1 | trim_2 | trim_3 | trim_4 | mois_01 | mois_02 | mois_03 | mois_04 | mois_05 | mois_06 | mois_07 | mois_08 | mois_09 | mois_10 | mois_11 | mois_12 | +--------+-------+---------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+ | 125 | 2016 | 1530.00 | 315.00 | 360.00 | 405.00 | 450.00 | 100.00 | 105.00 | 110.00 | 115.00 | 120.00 | 125.00 | 130.00 | 135.00 | 140.00 | 145.00 | 150.00 | 155.00 | | 217 | 2016 | 575.00 | 75.00 | 120.00 | 165.00 | 215.00 | 20.00 | 25.00 | 30.00 | 35.00 | 40.00 | 45.00 | 50.00 | 55.00 | 60.00 | 65.00 | 70.00 | 80.00 | +--------+-------+---------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+ -------------- COMMIT --------------   -------------- SET AUTOCOMMIT = 1 --------------   Appuyez sur une touche pour continuer...

---

@+