Requête SQL pivot ? [Résolu]

[jerome_88550]

Bonjour,

J'ai voulu essayé de faire une fonction pivot mais sans succés pourriez-vous me dire si la fonction pivot est bien celle qui faut que j'utilise pour faire ce dont je souhaite :

j'ai une table suivante :

Mat Nom    Type Hours Date
--- ------ ---- ----- ----------
001 jerome eff  7     03/12/2012
001 jerome th   8     03/12/2012
002 andree eff  6     03/12/2012
002 andree th   8     03/12/2012
001 jerome eff  8     04/12/2012
002 andree eff  8     04/12/2012

Je voudrais faire une requête select en SQL qui me retourne :

Mat Nom    eff th   Date
--- ------ --- ---- ----------
001 jerome 7   8    03/12/2012
002 andree 6   8    03/12/2012
001 jerome 8   NULL 04/12/2012
002 andree 8   NULL 04/12/2012

D'avance merci pour votre aide.

PS : La base de donnée est un serveur SQL 2012

Merci,

Jérôme

[Waldar]

Je vous confirme, c'est bien la fonctionnalité PIVOT dont vous avez besoin.

[islamov2000]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
WITH test AS
(       
SELECT '001'Mat, 'jerome' Nom, 'eff' Type, 7 Hours,    to_date('03/12/2012') jour FROM dual union 
SELECT '001', 'jerome', 'th',   8    , to_date('03/12/2012') FROM dual union 
SELECT '002', 'andree', 'eff',  6    , to_date('03/12/2012')FROM dual union 
SELECT '002', 'andree', 'th',   8    , to_date('03/12/2012')FROM dual union 
SELECT '001', 'jerome', 'eff',  8    , to_date('04/12/2012')FROM dual union 
SELECT '002', 'andree', 'eff',  8    , to_date('04/12/2012')FROM dual )
 
 
SELECT nvl(A.mat,B.mat) mat,nvl(A.nom,B.nom) nom,A.eff,B.th,nvl(A.jour,B.jour)jour FROM
 
  (SELECT mat,nom,jour,hours eff
   FROM test
   WHERE type='eff')A 
 
full   JOIN
 
  (SELECT mat,nom,jour,hours th
   FROM test
   WHERE type='th')B 
 
ON A.mat=B.mat AND A.jour=B.jour

Code :

1
2
3
4
5
6
 
   	MAT	NOM	EFF	TH	JOUR
1	001	jerome	7	8	03/12/2012
2	002	andree	6	8	03/12/2012
3	002	andree	8		04/12/2012
4	001	jerome	8		04/12/2012

[jerome_88550]

Merci Islamov2000,

Ton code m'as beaucoup aidé je dirais meme plus ça fonctionne avec ma table de production ^^

J'ai seulement utilisé COALESCE à la place de mvl car c'est sur un serveur SQL 2012.

Je te remercie encore de ton aide précieuse

@bientot car je doit integrer une deuxieme table pour avoir un peu plus de données si vraiment je n'y arrive pas je reviendrais demander de l'aide

Jérôme

[jerome_88550]

Bon finalement je séche encore ...

Voila l'exposé :

2 tables : ABSENCE , TOTAL

pour la table TOTAL :

Mat Nom    Type Hours Date
--- ------ ---- ----- ----------
001 jerome eff  7     03/12/2012
001 jerome th   8     03/12/2012
002 andree eff  6     03/12/2012
002 andree th   8     03/12/2012
001 jerome eff  8     04/12/2012
002 andree eff  8     04/12/2012
002 andree th   8     05/12/2012

Pour la table ABSENCE :

Mat Nom    Type2 Hours Date
--- ------ ---- ----- ----------
001 jerome cp   7     04/12/2012
002 andree rtt  6     05/12/2012

Il me faudrait enfaite combiner les 2 tables pour avoir ceci comme résultat

Mat Nom    eff  th    cp   rtt  Date
--- ------ ---- ----  ---- ---- ----------
001 jerome 7    8     NULL NULL 03/12/2012
002 andree 6    8     NULL NULL 03/12/2012
001 jerome 8    NULL  7    NULL 04/12/2012
002 andree 8    NULL  NULL NULL 04/12/2012
002 andree NULL 8     NULL 6    05/12/2012

Les champs des 2 tables sont identique sauf pour le "Type" .

D'avance merci pour votre soutient

Jerôme

[islamov2000]

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
WITH total AS
(       
SELECT '001'Mat  , 'jerome' Nom, 'eff' Type   , 7 Hours, to_date('03/12/2012') jour FROM dual union 
SELECT '001'     , 'jerome'    , 'th'         , 8      , to_date('03/12/2012') FROM dual union 
SELECT '002'     , 'andree'    , 'eff'        , 6      , to_date('03/12/2012')FROM dual union 
SELECT '002'     , 'andree'    , 'th'         , 8      , to_date('03/12/2012')FROM dual union 
SELECT '001'     , 'jerome'    , 'eff'        , 8      , to_date('04/12/2012')FROM dual union 
SELECT '002'     , 'andree'    , 'eff'        , 8      , to_date('04/12/2012')FROM dual union
SELECT '002'     , 'andree'    , 'th'         , 8      , to_date('05/12/2012')FROM dual),
absence AS
(SELECT '001' mat , 'jerome' nom , 'cp' type2 ,  7 hours, to_date('04/12/2012') jour FROM dual union
SELECT '002' mat , 'andree' nom , 'rtt' type2 ,  6 hours, to_date('05/12/2012') jour FROM dual
 
)
 
 
 
SELECT C.mat,C.nom,C.eff,C.th,F.cp,F.rtt,C.jour FROM 
---------------------------------------------------------------------------------------------
(SELECT nvl(A.mat,B.mat) mat,nvl(A.nom,B.nom) nom,A.eff,B.th,nvl(A.jour,B.jour)jour FROM
    (SELECT mat,nom,jour,hours eff
     FROM total
     WHERE type='eff')A --A
 
    full JOIN
 
    (SELECT mat,nom,jour,hours th
     FROM total
     WHERE type='th')B --B
 
    ON A.mat=B.mat AND A.jour=B.jour) C--C
----------------------------------------------------------------------------------------------
---------
full JOIN
-------- 
---------------------------------------------------------------------------------------------
(SELECT nvl(D.mat,E.mat) mat,nvl(D.nom,E.nom) nom,D.cp,E.rtt,nvl(D.jour,E.jour)jour FROM
 
    (SELECT mat,nom,jour,hours cp
     FROM absence
     WHERE type2='cp')D --D
 
    full JOIN
 
    (SELECT mat,nom,jour,hours rtt
     FROM absence
     WHERE type2='rtt')E--E
 
    ON D.mat=E.mat AND D.jour=E.jour) F --F
----------------------------------------------------------------------------------------------
ON C.mat=F.mat AND C.jour=F.jour
 
ORDER BY C.jour,C.mat

Code :

1
2
3
4
5
6
7
 
   	MAT	NOM	EFF	TH	CP	RTT	JOUR
1	001	jerome	7	8			03/12/2012
2	002	andree	6	8			03/12/2012
3	001	jerome	8		7		04/12/2012
4	002	andree	8				04/12/2012
5	002	andree		8		6	05/12/2012

[Waldar]

L'opérateur PIVOT reste plus rapide à utiliser ici quand même :

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
WITH total (Mat, Nom, [Type], [Hours], [Date]) AS
(       
SELECT '001', 'jerome', 'eff', 7, '03/12/2012' union ALL
SELECT '001', 'jerome', 'th' , 8, '03/12/2012' union ALL
SELECT '002', 'andree', 'eff', 6, '03/12/2012' union ALL
SELECT '002', 'andree', 'th' , 8, '03/12/2012' union ALL
SELECT '001', 'jerome', 'eff', 8, '04/12/2012' union ALL
SELECT '002', 'andree', 'eff', 8, '04/12/2012' union ALL
SELECT '002', 'andree', 'th' , 8, '05/12/2012'
)
  ,  absence (Mat, Nom, [Type2], [Hours], [Date]) AS
(
SELECT '001', 'jerome', 'cp' , 7, '04/12/2012' union ALL
SELECT '002', 'andree', 'rtt', 6, '05/12/2012'
)
  ,  totabs (Mat, Nom, [Type], [Hours], [Date]) AS
(
SELECT Mat, Nom, [Type] , [Hours], [Date] FROM total
 union ALL
SELECT Mat, Nom, [Type2], [Hours], [Date] FROM absence
)
  SELECT Mat, Nom, [eff], [th], [cp], [rtt], [Date]
    FROM totabs
   pivot (max([Hours]) FOR [Type] IN ([eff], [th], [cp], [rtt])) AS pvt
ORDER BY [Date] ASC, Mat ASC;
 
Mat  Nom    eff         th          cp          rtt         Date
---- ------ ----------- ----------- ----------- ----------- ----------
001  jerome 7           8           NULL        NULL        03/12/2012
002  andree 6           8           NULL        NULL        03/12/2012
001  jerome 8           NULL        7           NULL        04/12/2012
002  andree 8           NULL        NULL        NULL        04/12/2012
002  andree NULL        8           NULL        6           05/12/2012

[islamov2000]

@Waldar:
Est ce que PIVOT et UNPIVOT sont des fonctions de SQL standard.
En Oracle, ces fonctions ne sont supportées qu'à la version 11g.

[islamov2000]

Et je propose une autre solution plus simple en SQL standard.

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
 
WITH total AS
(       
SELECT '001'Mat  , 'jerome' Nom, 'eff' Type   , 7 Hours, to_date('03/12/2012') jour FROM dual union 
SELECT '001'     , 'jerome'    , 'th'         , 8      , to_date('03/12/2012') FROM dual union 
SELECT '002'     , 'andree'    , 'eff'        , 6      , to_date('03/12/2012')FROM dual union 
SELECT '002'     , 'andree'    , 'th'         , 8      , to_date('03/12/2012')FROM dual union 
SELECT '001'     , 'jerome'    , 'eff'        , 8      , to_date('04/12/2012')FROM dual union 
SELECT '002'     , 'andree'    , 'eff'        , 8      , to_date('04/12/2012')FROM dual union
SELECT '002'     , 'andree'    , 'th'         , 8      , to_date('05/12/2012')FROM dual),
absence AS
(SELECT '001' mat , 'jerome' nom , 'cp' type2 ,  7 hours, to_date('04/12/2012') jour FROM dual union
SELECT '002' mat , 'andree' nom , 'rtt' type2 ,  6 hours, to_date('05/12/2012') jour FROM dual
 
),
l_union AS
(SELECT * FROM total
union
SELECT * FROM absence
 )
 
SELECT mat,nom,
       SUM(CASE WHEN type = 'eff' THEN hours  END) eff,
       SUM(CASE WHEN type = 'th' THEN hours  END) th,
       SUM(CASE WHEN type = 'cp' THEN hours  END) cp,
       SUM(CASE WHEN type = 'rtt' THEN hours  END) rtt,
       jour
 
 
FROM l_union
GROUP BY mat,jour,nom
ORDER BY jour,mat

Code :

1
2
3
4
5
6
7
 
   	MAT	NOM	EFF	TH	CP	RTT	JOUR
1	001	jerome	7	8			03/12/2012
2	002	andree	6	8			03/12/2012
3	001	jerome	8		7		04/12/2012
4	002	andree	8				04/12/2012
5	002	andree		8		6	05/12/2012

[Chtulus]

Bonjour,

Est ce que PIVOT et UNPIVOT sont des fonctions de SQL standard

PIVOT est apparu sous SQL server 2005 et ne fait pas partis de la norme SQL, sauf erreur de ma part. @Waldar confirmera

Cordialement,

[Waldar]

À ma connaissance c'est une fonctionnalité hors-norme, mais c'est plutôt SQLPro qui pourra confirmer si ça a évolué depuis son article sur les nouveautés de SQL-Server 2005 :

Envoyé par SQLPro

L'horrible, mais traditionnel opérateur PIVOT, bien qu'absent de la norme SQL fait son apparition dans SQL Server 2005 afin de permettre de réaliser des tableaux croisés. Il s'agit simplement d'une astuce cosmétique pour présenter des données avec de nouvelles colonnes dont les noms sont extraits d'une des colonnes de la requête.

Mais comme c'est un besoin récurrent (il n'y a qu'a voir le nombre de demande sur ce forum), après son implémentation dans SQL-Server 2005 ça a été repompé par Oracle Database en version 11gR1.

On peut effectivement s'en passer avec la dernière méthode proposée par islamov2000, le seul avantage de la fonction PIVOT est qu'il est syntaxiquement plus rapide à écrire.

[jerome_88550]

Bonjour et merci pour toute vos réponse.

@Waldar :

J'ai testé la fonction pivot mais sans succés dans l'exemple vous avez créé une table "totabs" mais je doit pas passer par une table temporaire je peut seulement interoger la base je n'arrive donc pas à la faire fonctionner. c'est surement du a mon manque de connaisance en SQL :\

@Islamov2000 :

J'ai voulu tester votre solution plus simple en SQL , mais idem sans succé car j'ai une erreur sur le AS pour attribuer un nom a l'union ...

Pour info j'avais plus de type dans la table absence j'ai donc du modifier un peu le code mais je ne sais pas si c'est propre mais cela fonctionne :

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
SELECT C.matri,SUBSTRING (convert(varchar,C.dat,103),7,4) + SUBSTRING (convert(varchar,C.dat,103),4,2) + SUBSTRING (convert(varchar,C.dat,103),1,2)  dat,C.hjtheo,C.hjpoin,F.hjcong,F.hjrtt,I.hjmalad,I.hjcredhe,COALESCE(L.hjabsaut,L.hjabsaut2)hjabsaut FROM 
 
(SELECT COALESCE(A.EMPLOYEE_IDENTIFICATION_NUMBER,B.EMPLOYEE_IDENTIFICATION_NUMBER) matri,COALESCE(A.EMPLOYEE_SURNAME,B.EMPLOYEE_SURNAME) nom,A.hjpoin,B.hjtheo,COALESCE(A.TOTAL_DATE,B.TOTAL_DATE)dat FROM
    (SELECT EMPLOYEE_IDENTIFICATION_NUMBER,EMPLOYEE_SURNAME,TOTAL_DATE,HOURS hjpoin
     FROM ESTIMATED_DAILY_TOTAL
     WHERE TYPE_ABBREVIATION='eff')A
 
    full JOIN
 
    (SELECT EMPLOYEE_IDENTIFICATION_NUMBER,EMPLOYEE_SURNAME,TOTAL_DATE,HOURS hjtheo
     FROM ESTIMATED_DAILY_TOTAL
     WHERE TYPE_ABBREVIATION='th')B
 
    ON A.EMPLOYEE_IDENTIFICATION_NUMBER=B.EMPLOYEE_IDENTIFICATION_NUMBER AND A.TOTAL_DATE=B.TOTAL_DATE) C
 
 
full JOIN
 
(SELECT COALESCE(D.EMPLOYEE_IDENTIFICATION_NUMBER,E.EMPLOYEE_IDENTIFICATION_NUMBER) matri,COALESCE(D.EMPLOYEE_SURNAME,E.EMPLOYEE_SURNAME) nom,D.hjcong,E.hjrtt,COALESCE(D.TOTAL_DATE,E.TOTAL_DATE)dat FROM
 
    (SELECT EMPLOYEE_IDENTIFICATION_NUMBER,EMPLOYEE_SURNAME,TOTAL_DATE,HOURS hjcong
     FROM ESTIMATED_DAILY_ABSENCE
     WHERE ABS_TYPE_ABBREVIATION='CP')D 
 
    full JOIN
 
    (SELECT EMPLOYEE_IDENTIFICATION_NUMBER,EMPLOYEE_SURNAME,TOTAL_DATE,HOURS hjrtt
     FROM ESTIMATED_DAILY_ABSENCE
     WHERE ABS_TYPE_ABBREVIATION='RTT')E
 
    ON D.EMPLOYEE_IDENTIFICATION_NUMBER=E.EMPLOYEE_IDENTIFICATION_NUMBER AND D.TOTAL_DATE=E.TOTAL_DATE) F 
 
ON C.matri=F.matri AND C.dat=F.dat
 
full JOIN
 
(SELECT COALESCE(G.EMPLOYEE_IDENTIFICATION_NUMBER,H.EMPLOYEE_IDENTIFICATION_NUMBER) matri,COALESCE(G.EMPLOYEE_SURNAME,H.EMPLOYEE_SURNAME) nom,G.hjmalad,H.hjcredhe,COALESCE(G.TOTAL_DATE,H.TOTAL_DATE)dat FROM
 
    (SELECT EMPLOYEE_IDENTIFICATION_NUMBER,EMPLOYEE_SURNAME,TOTAL_DATE,HOURS hjmalad
     FROM ESTIMATED_DAILY_ABSENCE
     WHERE ABS_TYPE_ABBREVIATION='MAL')G 
 
    full JOIN
 
    (SELECT EMPLOYEE_IDENTIFICATION_NUMBER,EMPLOYEE_SURNAME,TOTAL_DATE,HOURS hjcredhe
     FROM ESTIMATED_DAILY_ABSENCE
     WHERE ABS_TYPE_ABBREVIATION='CH')H
 
    ON G.EMPLOYEE_IDENTIFICATION_NUMBER=H.EMPLOYEE_IDENTIFICATION_NUMBER AND G.TOTAL_DATE=H.TOTAL_DATE)I
 
ON C.matri=I.matri AND C.dat=I.dat
 
full JOIN
 
(SELECT J.EMPLOYEE_IDENTIFICATION_NUMBER matri,J.EMPLOYEE_SURNAME nom,J.hjabsaut,K.hjabsaut2,J.TOTAL_DATE dat FROM
 
    (SELECT EMPLOYEE_IDENTIFICATION_NUMBER,EMPLOYEE_SURNAME,TOTAL_DATE,HOURS hjabsaut
     FROM ESTIMATED_DAILY_ABSENCE
     WHERE ABS_TYPE_ABBREVIATION IN ('PAT','DELEG','RCTp'))J
 
	full JOIN
 
	(SELECT EMPLOYEE_IDENTIFICATION_NUMBER,EMPLOYEE_SURNAME,TOTAL_DATE,HOURS hjabsaut2
     FROM ESTIMATED_DAILY_ABSENCE
     WHERE ABS_TYPE_ABBREVIATION ='autre')K
 
	 ON J.EMPLOYEE_IDENTIFICATION_NUMBER=K.EMPLOYEE_IDENTIFICATION_NUMBER AND J.TOTAL_DATE=K.TOTAL_DATE)L
 
 
ON C.matri=L.matri AND C.dat=L.dat
 
WHERE C.matri ='51011' AND C.dat >= '20121201' AND C.dat <= '20121220'
 
ORDER BY C.dat,C.matri

Encore un grand merci à vous de m'avoir aidé.

Jérôme

[Waldar]

Envoyé par jerome_88550

J'ai testé la fonction pivot mais sans succés dans l'exemple vous avez créé une table "totabs" mais je doit pas passer par une table temporaire je peut seulement interoger la base je n'arrive donc pas à la faire fonctionner. c'est surement du a mon manque de connaisance en SQL :\

Il n'y a pas de table temporaire, la clause WITH permet, si vous préférez, de déclarer des vues en début de requête.
Compte tenu que vous avez déjà vos tables TOTAL et ABSENCE, votre requête doit commencer ici :

Code :

1
2
3
4
5
6
7
WITH totabs (Mat, Nom, [Type], [Hours], [Date]) AS
(
SELECT Mat, Nom, [Type] , [Hours], [Date] FROM total
 union ALL
SELECT Mat, Nom, [Type2], [Hours], [Date] FROM absence
)
SELECT ...

Laissez tomber les FULL JOIN, c'est la probablement la pire solution à ce problème.

[islamov2000]

Envoyé par jerome_88550

@Islamov2000 :

Pour info j'avais plus de type dans la table absence j'ai donc du modifier un peu le code mais je ne sais pas si c'est propre mais cela fonctionne :

Moi je recommande la deuxieme solution que j'ai proposé. elle est courte, plus lisible et facile à entretenir.

[jerome_88550]

Encore merci a vous

Donc j'ai pris la solution de islamov2000 et cela fonctionne comme je le souhaite.

J'ai du ajouté plusieurs type par colonne j'ai donc utilisé IN ( ... , ... , ... ) est-ce la meilleur methode ?

Sinon j'avais une interrogation comment il fait pour trouver le type2 car on lui demande de verifier si type = 'MAL' qui se trouve dans type2 ? ça fonctionne mais je voulais savoir comment


voici la requete modifié :

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
 
WITH l_union AS
(SELECT * FROM ESTIMATED_DAILY_TOTAL WHERE TYPE_ABBREVIATION = 'eff' OR TYPE_ABBREVIATION = 'th'
union
SELECT * FROM ESTIMATED_DAILY_ABSENCE
 )
 
SELECT EMPLOYEE_IDENTIFICATION_NUMBER matri,
       SUBSTRING (convert(varchar,TOTAL_DATE,103),7,4) + SUBSTRING (convert(varchar,TOTAL_DATE,103),4,2) + SUBSTRING (convert(varchar,TOTAL_DATE,103),1,2)  dat,
       SUM(CASE WHEN TYPE_ABBREVIATION = 'th' THEN HOURS  END) hjtheo,
       SUM(CASE WHEN TYPE_ABBREVIATION = 'eff' THEN HOURS  END) hjpoin,
       SUM(CASE WHEN TYPE_ABBREVIATION IN ('CP','CPA','CPR') THEN HOURS  END) hjcong,
       SUM(CASE WHEN TYPE_ABBREVIATION = 'RTT' THEN HOURS  END) hjrtt,
       SUM(CASE WHEN TYPE_ABBREVIATION IN ('MAL','MALNI','MAT','PAT','MP','AT','ATNI') THEN HOURS  END) hjmalad,
       SUM(CASE WHEN TYPE_ABBREVIATION = 'CH' THEN HOURS  END) hjcredhe,
       SUM(CASE WHEN TYPE_ABBREVIATION IN ('ABSA','ABSNA','ALT','CEM','CEF','CM','CSS','DELEG','DEP','FORM2','FORM','GREVE','MAP') THEN HOURS  END) hjabsaut
 
 
 
FROM l_union
 
WHERE EMPLOYEE_IDENTIFICATION_NUMBER = '51550' AND TOTAL_DATE >= '20121201' AND TOTAL_DATE <= '20121220'
 
GROUP BY EMPLOYEE_IDENTIFICATION_NUMBER,TOTAL_DATE
ORDER BY TOTAL_DATE,EMPLOYEE_IDENTIFICATION_NUMBER

Merci ,

Jérôme

[islamov2000]