Transformer une ligne en colonne

**Claude30120** · 04/03/2016, 17h24

Bonjour

J'ai un problème pour la construction d'une requête select.
J'explique :
Dans une table j'ai un enregistrement concernant un matériel loué de date à date.
Je voudrais obtenir n enregistrements (autant d'enregistrements qu'il y a de jours entre les deux dates de location) chacun portant le matériel loué et un jour

J'ai écrit la requête pour construire les jours
Création de la table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CREATE TABLE `location` (`rowid` INT NOT NULL AUTO_INCREMENT ,`fk_materiel` INT NULL ,`datedeb` DATE NULL ,`datefin` DATE NULL ,PRIMARY KEY ( `rowid` )
) ENGINE = InnoDB;

Ordre select

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
select fk_materiel, datedeb, datefin, 
case when diff1>=1 then ADDDATE(datedeb, INTERVAL 1 DAY) end as D1,
case when diff1 >= 2 then   ADDDATE(datedeb, INTERVAL 2 DAY)  end as D2,
case when diff1 >= 3  then ADDDATE(datedeb, INTERVAL 3 DAY)end  as D3,
case when diff1 > 4 then ADDDATE(datedeb, INTERVAL 4 DAY)end  as D4
from (    
     select fk_materiel, datedeb, datefin,      DATEDIFF( datefin, datedeb ) as diff1
    from location ) as Tb

Mais je n'ai toujours qu'une ligne
Comment faire pour obtenir n lignes

fk_materiel	datedeb
fk_materiel	D1	si D1 non null
fk_materiel	D2	si D2 non null
fk_materiel	D3	si D3 non null
fk_materiel	D4	si D4 non null
fk_materiel	datefin

Est-ce que quelqu'un a déjà fait cela?

Merci de votre aide éventuelle
Claude

**Artemus24** · 05/03/2016, 21h11

Bonjour Mme Claude.

Je sais, elle était facile celle là !!!

Ta solution ne fonctionne pas.
Car tu ne crées pas de lignes supplémentaires, mais juste des colonnes.
Dans mon exemple, j'ai repris ta solution.

Ensuite, il y a la méthode bourrin, qui consiste à utiliser le union, pour créer autant de ligne qu'il y de joueurs entre deux dates.
Tu peux constater que si tu te retrouves avec un intervalle de 200 jours de location, tu ne vas pas créer 200 union, comme je l'ai fait dans mon exemple.

La troisième solution est de créer une procédure stockée de remplissage d'une nouvelle table que j'ai nommé "ligne".
Dans cette table, qui est la reprise de la table location", tu as autant de lignes qu'il y a de jours entre tes deux dates.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET session collation_connection=latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `location`
--------------
 
--------------
CREATE TABLE `location`
(
  `rowid`        integer unsigned  NOT NULL AUTO_INCREMENT primary key,
  `fk_materiel`  integer unsigned  NOT NULL,
  `datedeb`      date                  NULL,
  `datefin`      date                  NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `location` (`fk_materiel`, `datedeb`, `datefin`) VALUES
(1, '2016-02-01', '2016-02-04'),
(2, '2016-02-12', '2016-02-16'),
(3, '2016-02-28', '2016-03-02')
--------------
 
--------------
select * from location
--------------
 
+-------+-------------+------------+------------+
| rowid | fk_materiel | datedeb    | datefin    |
+-------+-------------+------------+------------+
|     1 |           1 | 2016-02-01 | 2016-02-04 |
|     2 |           2 | 2016-02-12 | 2016-02-16 |
|     3 |           3 | 2016-02-28 | 2016-03-02 |
+-------+-------------+------------+------------+
--------------
select fk_materiel, datedeb, datefin,
  case when diff >= 1 then ADDDATE(datedeb, INTERVAL 1 DAY) end as D1,
  case when diff >= 2 then ADDDATE(datedeb, INTERVAL 2 DAY) end as D2,
  case when diff >= 3 then ADDDATE(datedeb, INTERVAL 3 DAY) end as D3,
  case when diff >  4 then ADDDATE(datedeb, INTERVAL 4 DAY) end as D4
from (
    select fk_materiel, datedeb, datefin, DATEDIFF(datefin, datedeb) as diff
    from location ) as Tb
--------------
 
+-------------+------------+------------+------------+------------+------------+------+
| fk_materiel | datedeb    | datefin    | D1         | D2         | D3         | D4   |
+-------------+------------+------------+------------+------------+------------+------+
|           1 | 2016-02-01 | 2016-02-04 | 2016-02-02 | 2016-02-03 | 2016-02-04 | NULL |
|           2 | 2016-02-12 | 2016-02-16 | 2016-02-13 | 2016-02-14 | 2016-02-15 | NULL |
|           3 | 2016-02-28 | 2016-03-02 | 2016-02-29 | 2016-03-01 | 2016-03-02 | NULL |
+-------------+------------+------------+------------+------------+------------+------+
--------------
select fk_materiel, case when DATEDIFF(datefin, datedeb) >= 0 then ADDDATE(datedeb, INTERVAL 0 DAY) end as jour from location having jour is not null
union select fk_materiel, case when DATEDIFF(datefin, datedeb) >= 1 then ADDDATE(datedeb, INTERVAL 1 DAY) end as jour from location having jour is not null
union select fk_materiel, case when DATEDIFF(datefin, datedeb) >= 2 then ADDDATE(datedeb, INTERVAL 2 DAY) end as jour from location having jour is not null
union select fk_materiel, case when DATEDIFF(datefin, datedeb) >= 3 then ADDDATE(datedeb, INTERVAL 3 DAY) end as jour from location having jour is not null
union select fk_materiel, case when DATEDIFF(datefin, datedeb) >= 4 then ADDDATE(datedeb, INTERVAL 4 DAY) end as jour from location having jour is not null
union select fk_materiel, case when DATEDIFF(datefin, datedeb) >= 5 then ADDDATE(datedeb, INTERVAL 5 DAY) end as jour from location having jour is not null
union select fk_materiel, case when DATEDIFF(datefin, datedeb) >= 6 then ADDDATE(datedeb, INTERVAL 6 DAY) end as jour from location having jour is not null
union select fk_materiel, case when DATEDIFF(datefin, datedeb) >= 7 then ADDDATE(datedeb, INTERVAL 7 DAY) end as jour from location having jour is not null
union select fk_materiel, case when DATEDIFF(datefin, datedeb) >= 8 then ADDDATE(datedeb, INTERVAL 8 DAY) end as jour from location having jour is not null
union select fk_materiel, case when DATEDIFF(datefin, datedeb) >= 9 then ADDDATE(datedeb, INTERVAL 9 DAY) end as jour from location having jour is not null
order by fk_materiel, jour
--------------
 
+-------------+------------+
| fk_materiel | jour       |
+-------------+------------+
|           1 | 2016-02-01 |
|           1 | 2016-02-02 |
|           1 | 2016-02-03 |
|           1 | 2016-02-04 |
|           2 | 2016-02-12 |
|           2 | 2016-02-13 |
|           2 | 2016-02-14 |
|           2 | 2016-02-15 |
|           2 | 2016-02-16 |
|           3 | 2016-02-28 |
|           3 | 2016-02-29 |
|           3 | 2016-03-01 |
|           3 | 2016-03-02 |
+-------------+------------+
--------------
DROP TABLE IF EXISTS `ligne`
--------------
 
--------------
CREATE TABLE `ligne`
(
  `numlig`       integer unsigned  NOT NULL AUTO_INCREMENT primary key,
  `fk_materiel`  integer unsigned  NOT NULL,
  `datedeb`      date                  NULL,
  `datefin`      date                  NULL,
  `datejour`     date                  NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP PROCEDURE IF EXISTS `remplir`
--------------
 
--------------
CREATE PROCEDURE `remplir`()
BEGIN
DECLARE _mat   INTEGER UNSIGNED DEFAULT NULL;
DECLARE _beg   DATE             DEFAULT NULL;
DECLARE _end   DATE             DEFAULT NULL;
DECLARE _day   DATE             DEFAULT NULL;
 
DECLARE _fin   INTEGER  DEFAULT 1;
 
DECLARE _tab CURSOR FOR SELECT fk_materiel, datedeb, datefin from location order by fk_materiel;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
 
 
 
OPEN _tab;
FETCH _tab INTO _mat, _beg, _end;
 
WHILE (_fin)
DO
  SET _day = _beg;
 
  WHILE (_day <= _end)
  DO
    INSERT INTO ligne (`fk_materiel`,`datedeb`,`datefin`,`datejour`) values (_mat, _beg, _end, _day);
    SET _day = ADDDATE(_day, INTERVAL 1 DAY);
  END WHILE;
 
  FETCH _tab INTO _mat, _beg, _end;
END WHILE;
 
CLOSE _tab;
END
--------------
 
--------------
call `remplir`
--------------
 
--------------
select * from ligne
--------------
 
+--------+-------------+------------+------------+------------+
| numlig | fk_materiel | datedeb    | datefin    | datejour   |
+--------+-------------+------------+------------+------------+
|      1 |           1 | 2016-02-01 | 2016-02-04 | 2016-02-01 |
|      2 |           1 | 2016-02-01 | 2016-02-04 | 2016-02-02 |
|      3 |           1 | 2016-02-01 | 2016-02-04 | 2016-02-03 |
|      4 |           1 | 2016-02-01 | 2016-02-04 | 2016-02-04 |
|      5 |           2 | 2016-02-12 | 2016-02-16 | 2016-02-12 |
|      6 |           2 | 2016-02-12 | 2016-02-16 | 2016-02-13 |
|      7 |           2 | 2016-02-12 | 2016-02-16 | 2016-02-14 |
|      8 |           2 | 2016-02-12 | 2016-02-16 | 2016-02-15 |
|      9 |           2 | 2016-02-12 | 2016-02-16 | 2016-02-16 |
|     10 |           3 | 2016-02-28 | 2016-03-02 | 2016-02-28 |
|     11 |           3 | 2016-02-28 | 2016-03-02 | 2016-02-29 |
|     12 |           3 | 2016-02-28 | 2016-03-02 | 2016-03-01 |
|     13 |           3 | 2016-02-28 | 2016-03-02 | 2016-03-02 |
+--------+-------------+------------+------------+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

**Claude30120** · 06/03/2016, 20h41

@Artemus24, bonsoir

Effectivement, elle est facile, elle est conuue en Roumanie, on me l'y a faite !!!

A part cela, merci pour tes infos, cela me met un pied à l'étrier des procédures sotckées.

Je met sur le fil une autre solution qui m'a été donnée par un ami informaticien

- créer une table de séquencement de 1 à ... le nombre maximum voulu
- faire un select avec un produit cartésien limité.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
 
CREATE TABLE jour( jour int);
insert into  jour values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
 
====================================
select fk_materiel, datedeb, datefin, 
case when jour = 0 then datedeb else ADDDATE(datedeb , INTERVAL jour+1 DAY) end as jourdesorti
 from (select * , DATEDIFF(datefin, datedeb ) as nbjour from  locaton ) as TB , jour 
WHERE  nbjour > jour or (DATEDIFF(datefin, datedeb )  = 0 and jour = 0) 
 
 
fk_materiel 	datedeb 	datefin 	jourdesorti
1 	2016-03-04 00:00:00 	2016-03-07 	2016-03-06 00:00:00
1 	2016-03-04 00:00:00 	2016-03-07 	2016-03-07 00:00:00
1 	2016-03-04 00:00:00 	2016-03-07 	2016-03-04 00:00:00
1 	2016-03-04 00:00:00 	2016-03-07 	2016-03-04 00:00:00
1 	2016-03-04 00:00:00 	2016-03-07 	2016-03-06 00:00:00
1 	2016-03-04 00:00:00 	2016-03-07 	2016-03-07 00:00:00

A+
Claude

**Artemus24** · 07/03/2016, 07h59

Salut Claude.

On ne peut pas créer de nouvelles lignes, sans utiliser les procédures stockées, si l'on n'a pas une table servant de support.
Cela, tu l'as compris, d'où l'intérêt de ta table contenant la séquence des jours (0, 1, 2, 3 ...).

J'ai testé ta méthode, mais elle ne fonctionne pas très bien. Il te manque 1 jour dans la séquence des lignes.
Je te laisse regarder le résultat que j'obtiens dans mon exemple ci-après.

J'ai repris ton exemple, en le simplifiant afin d'obtenir le stricte nécessaire.
L’inconvénient, que ce soit de la méthode bourrin, ou de la tienne, repose sur le nombre de lignes à créer.
Si maintenant, tu as 200 lignes à produire, il te faut 200 lignes dans ta table jour, où bien créer 200 union, ce qui va être plutôt long en performance.
La solution reste quand même la procédure stockée.

Et voici le nouveau test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET session collation_connection=latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `location`
--------------
 
--------------
CREATE TABLE `location`
(
  `rowid`        integer unsigned  NOT NULL AUTO_INCREMENT primary key,
  `fk_materiel`  integer unsigned  NOT NULL,
  `datedeb`      date                  NULL,
  `datefin`      date                  NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `location` (`fk_materiel`, `datedeb`, `datefin`) VALUES
(1, '2016-02-01', '2016-02-04'),
(2, '2016-02-12', '2016-02-16'),
(3, '2016-02-28', '2016-03-02')
--------------
 
--------------
select * from location
--------------
 
+-------+-------------+------------+------------+
| rowid | fk_materiel | datedeb    | datefin    |
+-------+-------------+------------+------------+
|     1 |           1 | 2016-02-01 | 2016-02-04 |
|     2 |           2 | 2016-02-12 | 2016-02-16 |
|     3 |           3 | 2016-02-28 | 2016-03-02 |
+-------+-------------+------------+------------+
--------------
DROP TABLE IF EXISTS `jour`
--------------
 
--------------
CREATE TABLE `jour`
(
  `jour` integer unsigned  NOT NULL primary key
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `jour` (`jour`) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
--------------
 
--------------
select * from jour
--------------
 
+------+
| jour |
+------+
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
--------------
select fk_materiel, datedeb, datefin, case when jour = 0 then datedeb else ADDDATE(datedeb , INTERVAL jour+1 DAY) end as jourdesorti
from (select * , DATEDIFF(datefin, datedeb) as nbjour from  location) as TB, jour
WHERE  nbjour > jour or (DATEDIFF(datefin, datedeb )  = 0 and jour = 0)
order by fk_materiel, jourdesorti
--------------
 
+-------------+------------+------------+-------------+
| fk_materiel | datedeb    | datefin    | jourdesorti |
+-------------+------------+------------+-------------+
|           1 | 2016-02-01 | 2016-02-04 | 2016-02-01  |
|           1 | 2016-02-01 | 2016-02-04 | 2016-02-03  |
|           1 | 2016-02-01 | 2016-02-04 | 2016-02-04  |
|           2 | 2016-02-12 | 2016-02-16 | 2016-02-12  |
|           2 | 2016-02-12 | 2016-02-16 | 2016-02-14  |
|           2 | 2016-02-12 | 2016-02-16 | 2016-02-15  |
|           2 | 2016-02-12 | 2016-02-16 | 2016-02-16  |
|           3 | 2016-02-28 | 2016-03-02 | 2016-02-28  |
|           3 | 2016-02-28 | 2016-03-02 | 2016-03-01  |
|           3 | 2016-02-28 | 2016-03-02 | 2016-03-02  |
+-------------+------------+------------+-------------+
--------------
select fk_materiel,
       datedeb,
       datefin,
       adddate(datedeb, interval jour day) as jourdesorti
from location, jour
having jourdesorti <= datefin
order by fk_materiel, jourdesorti
--------------
 
+-------------+------------+------------+-------------+
| fk_materiel | datedeb    | datefin    | jourdesorti |
+-------------+------------+------------+-------------+
|           1 | 2016-02-01 | 2016-02-04 | 2016-02-01  |
|           1 | 2016-02-01 | 2016-02-04 | 2016-02-02  |
|           1 | 2016-02-01 | 2016-02-04 | 2016-02-03  |
|           1 | 2016-02-01 | 2016-02-04 | 2016-02-04  |
|           2 | 2016-02-12 | 2016-02-16 | 2016-02-12  |
|           2 | 2016-02-12 | 2016-02-16 | 2016-02-13  |
|           2 | 2016-02-12 | 2016-02-16 | 2016-02-14  |
|           2 | 2016-02-12 | 2016-02-16 | 2016-02-15  |
|           2 | 2016-02-12 | 2016-02-16 | 2016-02-16  |
|           3 | 2016-02-28 | 2016-03-02 | 2016-02-28  |
|           3 | 2016-02-28 | 2016-03-02 | 2016-02-29  |
|           3 | 2016-02-28 | 2016-03-02 | 2016-03-01  |
|           3 | 2016-02-28 | 2016-03-02 | 2016-03-02  |
+-------------+------------+------------+-------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

**Claude30120** · 07/03/2016, 17h37

Bonjour Artemus24

Sur que ma méthode est un peu brute de fonderie pour des intervalles de dates importants: pour moi : 10 jours, c'est le maximum
Merci pour la simplification. Une question de détail : pourquoi utiliser having et non where

Je ferai appel à toi, si j'ai d'autres requêtes compliquées, il me semble avoir trouver une belle ressource !!!!

Cordialement
Claude

**Artemus24** · 08/03/2016, 08h54

Salut claude.

Envoyé par claude30120

pourquoi utiliser having et non where

Que ce soit le "where", le "having" ou le "on", ces clauses permettent de définir un critère de sélection.
Sauf que la sélection ne se fait pas au même niveau dans l'extraction des données.

Le "on", c'est au moment de la jointure entre deux tables.
Le "where", c'est après la jointure, au moment de la lecture des lignes.
Le "having", c'est après la lecture, et après les regroupements.

Normalement, le "having" s'utilise conjointement avec un "group by".
Mais il a le mérite de pouvoir référencer une colonne calculée, ce qui n'est pas le cas avec le "where".

Sauf qu'il faut obligatoirement un "group by" si tu utilises les fonctions d'agrégations comme "count()", "sum()", "max(), "min()".

@+

**Claude30120** · 09/03/2016, 09h12

@Artemus24
Merci pour ces précisions.
Ta façon de remplacer where par having m'interesse au plus haut point,
pour alléger la requête en supprimant la table temporaire from (select ...) as TB qui, pour moi était la seule façon de pouvoir filtrer sur une colonne calculée.

Merci
Claude

**Artemus24** · 09/03/2016, 11h47

Salut claude.

Quel est la solution que tu as retenue ?

@+

Transformer une ligne en colonne [MySQL-5.0]

Requêtes MySQL

Discussions similaires

Partager

Partager