Aide: présentation du résultat d'une requête

Version imprimable

Bonjour,

Je cherche à présenter le résultat de ma requête sous une forme concaténée:

1=> code, nom, groupe, pc1, pc2, pc3, pc4, pc5.

Le seul hic est que je n'arrive pas à forcer le résultat dans cette présentation.
Je l'ai plutôt naturellement comme ceci :

2=> code, non, groupe, pc.
aa, libelle de aa, groupe1, pc1
bb, libelle de bb, groupe1, pc2
...

Comment faire pour que 2 se présente comme 1 ?

Voici mon humble requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT DISTINCT
  A.code                                                                AS CODE, 
  A.intitule_code                                                       AS NOM,
  B.libelle                                                             AS GROUPE, 
  CASE  WHEN E.id = 1		THEN '1' Else '0' END   AS "PC1",
  CASE  WHEN E.id = 2          	THEN '1' Else '0' END   AS "PC2",
  CASE  WHEN E.id = 3          	THEN '1' Else '0' END   AS "PC3",
  CASE  WHEN E.id = 4       	THEN '1' Else '0' END   AS "PC4",
  CASE  WHEN E.id = 5		THEN '1' Else '0' END   AS "PC5"
FROM 
  base.code_1 AS A
        LEFT JOIN base.asso_code_1_sc AS C
                ON A.id_code = C.id_code AND A.id_groupe = C.id_groupe
                        LEFT JOIN base.plancpt_lib AS E
                                ON C.id_plan = E.id
        LEFT JOIN base.groupes_codes_adm AS B
                ON A.id_groupe = B.id AND B.id = C.id_groupe;

Merci.

16/02/2015, 12h19
Waldar

Vous n'êtes pas très loin, l'opération que vous chercher à faire est un PIVOT.
Il y a des fonctions natives postgresql (type crosstab), ou la solution SQL compatible multi-SGBD que vous avez initié.
Cherchez sur le forum dans Langage SQL par exemple, il y a de nombreux exemples.

Bonjour,

Il semble que le module tablefunc ne soit pas installé et donc je ne peux pas utiliser CROSSTAB.

Comment je fais ?

Je suis sous debian wheezy.

PS: En lançant le code suivant :

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
SELECT * FROM CROSSTAB(
	'SELECT DISTINCT ON (CODE,GROUPE)
	A.code, 
	A.intitule_code,
	B.libelle,
	E. intitule
	FROM
	base.code_1 AS A
		LEFT JOIN base.groupes_codes_adm AS B
			ON A.id_groupe = B.id AND B.id = C.id_groupe
		LEFT JOIN base.asso_code_1_sc AS C
			ON A.id_code = C.id_code AND A.id_groupe = C.id_groupe AND A.id_code =1
			LEFT JOIN base.plancpt_lib AS E
                                ON C.id_plan = E.id'
)
 AS
 (
	CODE character varying(200),
	INTITULE character varying(200),
	GROUPE character varying(100),
	"PC1" character varying(50),
	"PC2" character varying(50),
	"PC3" character varying(50),
	"PC4" character varying(50),
	"PC5" character varying(50)
);

j'obtiens :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
ERREUR:  la fonction crosstab(unknown) n'existe pas
LINE 1:   SELECT * FROM CROSSTAB(
                        ^
HINT:  Aucune fonction ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
 
********** Erreur **********
 
ERREUR: la fonction crosstab(unknown) n'existe pas
État SQL :42883
Astuce : Aucune fonction ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
Caractère : 17

J'en profite aussi pour savoir si quelqu'un peut me valider la modification de mon code qui utilise CROSSTAB ?

Cordialement,
vandman

Je viens de trouver la réponse:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT DISTINCT ON (CODE,GROUPE)
  A.code                                                     AS CODE, 
  A.intitule_code                                            AS NOM,
  B.libelle                                                  AS GROUPE, 
  SUM(CASE  WHEN E.id = 1		THEN 1 Else 0 END)   AS "PC1",
  SUM(CASE  WHEN E.id = 2          	THEN 1 Else 0 END)   AS "PC2",
  SUM(CASE  WHEN E.id = 3          	THEN 1 Else 0 END)   AS "PC3",
  SUM(CASE  WHEN E.id = 4       	THEN 1 Else 0 END)   AS "PC4",
  SUM(CASE  WHEN E.id = 5		THEN 1 Else 0 END)   AS "PC5"
FROM 
  base.code_1 AS A
        LEFT JOIN base.asso_code_1_sc AS C
                ON A.id_code = C.id_code AND A.id_groupe = C.id_groupe
                        LEFT JOIN base.plancpt_lib AS E
                                ON C.id_plan = E.id
        LEFT JOIN base.groupes_codes_adm AS B
                ON A.id_groupe = B.id AND B.id = C.id_groupe
GROUP BY 1,2,3
ORDER BY 1;

L'erreur que j'ai commise est que dans ma table d'association, c.id_code existe n fois pour un plan donné. De plus j’essayais d'agréger du texte, ce qui est difficile en soit.
Merci tout de même pour l'aide car je continue à progresser.
Cordialement,
vandman

16/02/2015, 19h12
Waldar

Votre DISTINCT ON ici est inutile, cette opération étant déjà réalisée par la clause GROUP BY.
Attention également au GROUP BY 1,2,3 qui est permis par PostgreSQL et MySQL, mais ce n'est pas dans la norme SQL et ça ne fonctionne pas sur Oracle ou SQL-Server.
Préférez un simple copier / coller !
Pas de soucis pour la clause ORDER BY par contre.
17/02/2015, 11h01
vandman

Merci Waldar,

J'ai corrigé le code pour être au plus près du standard.

Cordialement
20/02/2015, 10h25
vandman

Bonjour,

Je reviens vers vous car j'ai remarqué que la commande ne fonctionne pas comme je veux. Je ne l'ai pas vu tout de suite car j'ai eu comme résultat 225 000 lignes et je n'ai pas vu tout de suite que le couple code - sous-compte me générait des doublons. En effet, la relation entre le code, le sous compte, le plan comptable et le groupe est de 1 (code) pour n (sous-compte, plan comptable et groupe). Je m'étais trompé auparavant.

Comment réaliser une fonction PIVOT sans que j'aie accès au module tablefunc ?

Cordialement,
Vandman

Citation:

Envoyé par vandman

Comment réaliser une fonction PIVOT sans que j'ai accès au module tablefunc?

Comme vous l'avez fait, en SQL avec des sum(case).

Finalement ce problème c'est un peu le même que dans l'autre sujet.
Si vos jointures travaillent sur des cardinalités (1,n), il faut faire votre agrégat avant ces jointures afin de ne pas démultiplier les chiffres.

Essayez comme ceci :

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
with cte_asso_cod_plancpt_lib as
(
    select c.id_code
         , c.id_groupe
         , sum(case e.id when 1 then 1 else 0 end) as pc1
         , sum(case e.id when 2 then 1 else 0 end) as pc2
         , sum(case e.id when 3 then 1 else 0 end) as pc3
         , sum(case e.id when 4 then 1 else 0 end) as pc4
         , sum(case e.id when 5 then 1 else 0 end) as pc5
      from base.asso_code_1_sc as c
 left join base.plancpt_lib    as e on e.id = c.id_plan
  group by c.id_code
         , c.id_groupe
)
    select a.code
         , a.intitule_code          as nom
         , b.libelle                as groupe
         , coalesce(c.pc1, 0)       as pc1
         , coalesce(c.pc2, 0)       as pc2
         , coalesce(c.pc3, 0)       as pc3
         , coalesce(c.pc4, 0)       as pc4
         , coalesce(c.pc5, 0)       as pc5
      from base.code_1              as a
 left join base.groupes_codes_adm   as b  on b.id        = a.id_groupe
 left join cte_asso_cod_plancpt_lib as c  on c.id_code   = a.id_code
                                         and c.id_groupe = a.id_groupe
  order by a.code asc;

Bonjour,
J'y suis enfin arrivé.
Si quelqu'un veut faire des commentaires pour améliorer le code, n'hésitez pas!

petite explication:
j'utilise 5 tables:
asso_code_A_sc => associe le code (id_code), le sous-compte (no_compte) et le plan comptable (id_plan)
code_A => liste les codes; infos utiles = id du code, code + intitulé, id du groupe d'appartenance (id_groupe).
groupes_codes => liste le groupe de code et son type (dépense et recette); infos utiles => id du code, type de code (type_depenses_rec)
sous_compte => liste les sous-comptes et leur associations au plan comptable; infos utiles => l'id du plan (id_plan), le sous-compte est égale à SC.num_cpte si id_parent = 0 si non le sous-compte = (SC.codcpt || SC.num_cpte)
rmo => liste les dépense et les recettes en les associant au sous-compte et au code; infos utiles => le code (code_a), le montant de la dépense (somme des positifs et somme des négatif) et de la recette (somme des positifs et somme des négatif) > je fais ca pour ne pas me faire avoir par des sommes = 0, cptdeb et cptcred qui liste les sous-compte pour les débits et les crédits.

Pour rappel, je cherche à savoir quels sont les codes existants qui sont utilisés ou non et à quel sous-compte, groupe, plan ils sont associés.

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
WITH ass_ca_sc AS  
(
SELECT	CA.code								AS code_A
	,Gr.id								AS GC
	,CASE	WHEN id_parent = 0	THEN num_cpte
		Else codcpt || num_cpte
		END							AS sous_compte
	,A_CA_SC.id_plan						AS AS_PC
FROM base.asso_code_A_sc AS A_CA_SC
	RIGHT JOIN base.code_A AS CA
		ON A_CA_SC.id_code = CA.id_code
		LEFT JOIN base.groupes_codes AS Gr
			ON CA.id_groupe = Gr.id
	LEFT JOIN base.sous_compte AS SC
		ON A_CA_SC.id_plan = SC.id_plan AND CAST (A_CA_SC.no_compte AS INTEGER) = SC.id_souscompte
GROUP BY CA.id_code, code_A, GC, sous_compte, AS_PC
ORDER BY code_A, sous_compte asc
),
Somme_2014 AS
(
SELECT	CA.code														AS code_A
	,CASE	WHEN id_parent = 0	THEN num_cpte
		Else codcpt || num_cpte
		END													AS sous_compte	
	,SUM(	CASE	WHEN Gr.type_depenses_rec = 1	AND	Rm.montant < 0	THEN
				CASE	WHEN (CASE	WHEN SC.id_parent = 0	THEN	SC.num_cpte
							Else SC.codcpt || SC.num_cpte
							END) = Rm.cptdeb	THEN	Rm.montant
					Else 0 END
			WHEN Gr.type_depenses_rec = 2	AND	Rm.montant < 0	THEN													
				CASE	WHEN (CASE    WHEN SC.id_parent = 0 	THEN SC.num_cpte
							Else SC.codcpt || SC.num_cpte
							END) = Rm.cptcred	THEN	Rm.montant
					Else 0 END
			ELSE 0
			END)												AS mA2014
	,SUM(	CASE	WHEN Gr.type_depenses_rec = 1	AND	Rm.montant > 0	THEN
				CASE	WHEN (CASE	WHEN SC.id_parent = 0		THEN SC.num_cpte
							Else SC.codcpt || SC.num_cpte
							END) = Rm.cptdeb	THEN	Rm.montant
					Else 0 END
			WHEN Gr.type_depenses_rec = 2 AND Rm.montant > 0	THEN													
				CASE	WHEN (CASE	WHEN SC.id_parent = 0		THEN SC.num_cpte
							Else SC.codcpt || SC.num_cpte
							END) = Rm.cptcred	THEN	Rm.montant
					Else 0 END
			ELSE 0
			END)												AS pA2014
 
FROM base.asso_code_A_sc AS A_CA_SC
	RIGHT JOIN base.code_A AS CA
		ON A_CA_SC.id_code = CA.id_code
		LEFT JOIN base.groupes_codes AS Gr
			ON CA.id_groupe = Gr.id
	LEFT JOIN base.sous_compte AS SC
		ON CAST(A_CA_SC.no_compte AS INTEGER) = SC.id_souscompte AND A_CA_SC.id_plan = SC.id_plan
	LEFT JOIN base.rmo AS Rm
		ON A_CA_SC.id_code = Rm.code_a AND	CASE	WHEN Gr.type_depenses_rec = 1	THEN
									CASE	WHEN SC.id_parent = 0	THEN SC.num_cpte
										Else SC.codcpt || SC.num_cpte
										END = Rm.cptdeb
								ELSE	CASE	WHEN SC.id_parent = 0	THEN SC.num_cpte
										Else SC.codcpt || SC.num_cpte
										END = Rm.cptcred 
								END
GROUP BY code_A, sous_compte
ORDER BY code_A, sous_compte asc
)
SELECT	
	ass_ca_sc.code_A
	,ass_ca_sc.sous_compte
	,SUM(CASE	WHEN GC = 1		THEN 1			ELSE 0 END)	AS CA_depensesS
	,SUM(CASE	WHEN GC = 2		THEN 1			ELSE 0 END)	AS CB_depensesS
	,SUM(CASE	WHEN GC = 3		THEN 1			ELSE 0 END)	AS CA_RECETTES
	,SUM(CASE	WHEN GC = 4		THEN 1			ELSE 0 END)	AS CA_RECETTES
	,SUM(CASE	WHEN AS_PC = 1		THEN 1			ELSE 0 END)	AS "PC1"
	,SUM(CASE	WHEN AS_PC = 2		THEN 1			ELSE 0 END)	AS "PC2"
	,SUM(CASE	WHEN AS_PC = 3		THEN 1			ELSE 0 END)	AS "PC3"
	,SUM(CASE	WHEN AS_PC = 4		THEN 1			ELSE 0 END)	AS "PC4"
	,SUM(CASE	WHEN AS_PC = 5		THEN 1 			ELSE 0 END)	AS "PC5"
	,SUM(CASE	WHEN S2014.mA2014 <> 0	THEN S2014.mA2014	ELSE 0 END)	AS "-2014"
	,SUM(CASE	WHEN S2014.pA2014 <> 0	THEN S2014.pA2014	ELSE 0 END)	AS "+2014"
FROM ass_ca_sc
	LEFT JOIN Somme_2014 AS S2014
		ON ass_ca_sc.code_A = S2014.code_A AND ass_ca_sc.sous_compte = S2014.sous_compte
GROUP BY ass_ca_sc.code_A
,ass_ca_sc.sous_compte
ORDER BY CA_depensesS
,ass_ca_sc.code_A
,ass_ca_sc.sous_compte
asc;

Voila!

Merci à Waldar

Codialement,
vandman