Discussion :

[Luern]

Bonjour,

Sous postgres, je possède une table représentant des centaines de logements (une ligne= un logement). Cette table possède plusieurs dizaines de champs dont un champ commune et un champ année.

J'aimerais aboutir à un tableau avec en ligne mes communes (groupées bien sur) et en colonne les années. J'arrive à un résultat approximatif avec cette requête:

Code :

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

1
2
3
4
5
6
7
8
SELECT  libelle_commune, annee_entree_lgmt_patrimoine_loc_bailleur, count(*)
 
FROM dreal.rpls
 
GROUP BY libelle_commune, annee_entree_lgmt_patrimoine_loc_bailleur
 
ORDER BY  libelle_commune, annee_entree_lgmt_patrimoine_loc_bailleur
;

Le résultat est une table avec

commune|année|count du nb de logement
A 1969 54
A 1982 32
A 2010 02
B 1983 05
B 1990 10
C 2005 12

Et en clair, je souhaiterais arriver à une structure comme celle-ci:

1969|1982|1983|1990|2005|2010
A 54 32 02
B 05 10
C 12


Je vous remercie d'avance beaucoup!

[al1_24]

Tu ne précises pas avec quel SGBD tu travailles...
Sinon, ce que tu cherches à faire se nomme PIVOT en SQL.

[punkoff]

bonjour,

Tournez vous vers la fonction String_agg par exemple.
http://www.postgresql.org/docs/9.2/s...aggregate.html

[Luern]

Je me trouve sous postgres 9.2

Je regarde ces différentes fonctions. merci

[Waldar]

Vous pouvez essayer la fonction crosstab() :
http://www.postgresql.org/docs/curre...tablefunc.html

[Luern]

En utilisant crosstab, je n'arrive pas faire marcher la requête suivante:

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
 
SELECT * 
 
FROM crosstab ('
 
SELECT  libelle_commune, count(*)
 
FROM dreal.rpls
 
GROUP BY libelle_commune
 
ORDER BY  libelle_commune')
 
AS rpls (libelle_commune, "1966" ,"1969","1973","1974","1977","1980","1982","1983","1984","1985","1986","1987","1988","1989","1991","1992","1993","1994","1995","1997","1998","1999","2000","2001","2002","2003","2004","2005","2006","2007","2009","2010","2011")
;

qui me renvoie l'erreur suivante:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
ERREUR:  une liste de définition de colonnes est requise pour les fonctions renvoyant
un « record »
LINE 4: FROM crosstab ('
             ^
 
********** Erreur **********
 
ERREUR: une liste de définition de colonnes est requise pour les fonctions renvoyant
un « record »
État SQL :42601
Caractère : 18

Quelqu'un a-t-il une idée svp?

[estofilo]

La raison de ce message d'erreur est qu'il manque les types de données dans la "liste de définition de colonnes", c.a.d ce genre de syntaxe:

Code :

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

...AS rpls (libelle_commune TEXT, "1966"  TEXT, "1969" TEXT, etc...

(sauf que c'est peut être INT au lieu de TEXT, à adapter)

Une fois ceci réglé il y aura un autre problème, c'est que la requête à l'intérieur du crosstab doit sortir 3 colonnes et non 2, ces 3 colonnes correspondant à nom, catégorie, valeur.

Les noms vont aller dans la 1ere colonne du tableau obtenu
Les catégories vont former les noms des colonnes 2 à N du tableau obtenu
Chaque valeur correspond à un couple (nom,catégorie) et va se retrouver dans la cellule (au sens tableur) dont la ligne est le nom et la colonne la catégorie.

[Luern]

En testant plusieurs solutions, je n'arrive toujours pas à faire marcher le crosstab (cf code ci dessous).

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT * 
 
FROM crosstab ('
 
SELECT  libelle_commune, annee_entree_lgmt_patrimoine_loc_bailleur, count(*)
 
FROM dreal.rpls
 
GROUP BY libelle_commune, annee_entree_lgmt_patrimoine_loc_bailleur
 
ORDER BY  libelle_commune')
 
AS rpls (libelle_commune TEXT, "1966" TEXT,"1969" TEXT,"1973" TEXT,"1974" TEXT,"1977" TEXT,"1980" TEXT,"1982" TEXT,"1983" TEXT,"1984" TEXT,"1985" TEXT,"1986" TEXT,"1987" TEXT,"1988" TEXT,"1989" TEXT,"1991" TEXT,"1992" TEXT,"1993" TEXT,"1994" TEXT,"1995" TEXT,"1997" TEXT,"1998" TEXT,"1999" TEXT,"2000" TEXT,"2001" TEXT,"2002" TEXT,"2003" TEXT,"2004" TEXT,"2005" TEXT,"2006" TEXT,"2007" TEXT,"2009" TEXT,"2010" TEXT,"2011" TEXT)
;

Et j'ai l'erreur qui s'affiche:

Code :

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

1
2
3
4
5
6
7
8
ERREUR:  invalid return type
DETAIL:  SQL rowid datatype does not match return rowid datatype.
 
********** Erreur **********
 
ERREUR: invalid return type
État SQL :42601
Détail :SQL rowid datatype does not match return rowid datatype.

[Waldar]

Remplacez vos TEXT par des INT sauf pour libelle_commune.

[Luern]

mais (mm si ce n'est pas correct) mon champ année est en text.

Est ce tout de même une source de bug?

[estofilo]

Il faut voir que c'est que ce n'est pas l'année qui sort au final dans ces colonnes 2->N mais le résultat d'un count(*).
Le type correspondant est BIGINT.

Voici une démo simplifiée:

Code :

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

1
2
3
4
5
6
create table t1(libelle_commune text,annee text);
insert into t1 values('Paris','1970'),('Paris','1970'),('Paris','1971');
insert into t1 values('Lyon','1970'),('Lyon','1971'),('Lyon','1971');
 
select * from crosstab('select libelle_commune,annee,count(*) from t1 group by libelle_commune,annee order by 1,2')
 AS ct(commune text,"1970" bigint,"1971" bigint);

Résultat:

 commune | 1970 | 1971 
---------+------+------
 Lyon    |    1 |    2
 Paris   |    2 |    1

Dans cet exemple il y a des données pour tous les couples (ville,année). S'il manque des données il faut utiliser une variante de crosstab à 2 arguments, le 2eme étant une requête qui liste toutes les valeurs possibles des années dans l'ordre.

Par exemple en ajoutant 1972 pour laquelle il n'y pas d'entrée pour Lyon ou Paris:

Code :

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

 insert into t1 values('Marseille','1972');

Nouvelle requête (avec generate_series() puisque ce sont des valeurs consécutives, sinon on pourrait mettre une clause VALUES avec une énumération à la place, ça reviendrait au même)

Code :

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

1
2
3
select * from crosstab('select libelle_commune,annee,count(*) from t1 group by libelle_commune,annee order by 1,2',
'select * from generate_series(1970,1972)'
) AS ct(commune text,"1970" bigint,"1971" bigint,"1972" bigint);

Résultat:

  commune  | 1970 | 1971 | 1972 
-----------+------+------+------
 Lyon      |    1 |    2 |     
 Marseille |      |      |    1
 Paris     |    2 |    1 |     

Attention au fait que si on utilise la première forme alors qu'il y a des "trous" dans le tableau, il n'y a pas de message d'erreur mais le résultat est faux.

Exemple:

Code :

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

1
2
3
 
select * from crosstab('select libelle_commune,annee,count(*) from t1 group by libelle_commune,annee order by 1,2') 
AS ct(commune text,"1970" bigint,"1971" bigint,"1972" bigint);

Résultat faux:

  commune  | 1970 | 1971 | 1972 
-----------+------+------+------
 Lyon      |    1 |    2 |     
 Marseille |    1 |      |     
 Paris     |    2 |    1 |     

[Luern]

Merci beaucoup !!