Récupérer plusieurs lignes et les afficher dans une seule colonne et un seul champ

**LudoV63** · 30/01/2019, 11h24

Bonjour,

Je bosse sur une base de données ÉLÈVES et je dois extraire de cette base les noms, prénoms, âge, sexe, classe, options, etc .....

Hormis les OPTIONS, tout les champs que je dois récupérer s'affiche correctement.

Il peut y avoir plusieurs OPTIONS par élève (de 1 à 10) ou bien aucune option selon les élèves.
Chaque option à bien évidemment une clé unique OPTION_ID

Exemple:
Pour un élève, j'ai 3 options :

ALL1	BRYAN
AGL2	BRYAN
ESP3	BRYAN

Je souhaite que le résultat retourné ressemble à ceci :

BRYAN

ALL1, AGL2, ESP3

J'ai parcouru nombreux forum et il semblerait que je dois faire une FONCTION pour pouvoir obtenir le résultat que je souhaite.
J'ai essayé GROUP_CONCAT, CONCAT, CONCAT_WS, mais rien à y faire ...
Je dois probablement mal syntaxé ma requête.

Pourriez-vous me venir en aide car je ne parviens pas à faire cette fonction ?

Je vous souhaite une belle journée à toutes et tous.

**Darkzinus** · 30/01/2019, 13h49

Bonjour pour faire ce genre d'opération j'utilise la fonction xmlserialize.
https://www.ibm.com/support/knowledg...serialize.html
A la suite de ça tu peux chercher le mots clé : xmlagg, xmltxt

**escartefigue** · 30/01/2019, 19h52

Envoyé par Darkzinus

Bonjour pour faire ce genre d'opération j'utilise la fonction xmlserialize.
https://www.ibm.com/support/knowledg...serialize.html
A la suite de ça tu peux chercher le mots clé : xmlagg, xmltxt

Jamais utilisé, quelques exemples avec un échantillon des données en entrée et du résultat obtenu seraient les bienvenus

**bernard59139** · 31/01/2019, 01h44

Je prefererais utiliser LISTAGG https://www.ibm.com/support/knowledg...f_listagg.html
a+

**LudoV63** · 31/01/2019, 07h47

Salut à tous,

Merci pour ces 2 liens, je vais essayer de regarder ça.

Pour les données tests et le résultat attendu c'est ce que j'ai mis dans le premier post en fait, 1 élève peut avoir plusieurs options (angl, allemand, etc ...) qui apparaissent en BDD sur plusieurs lignes car chaque option à son ID et je voudrais que ces résultats soit concaténer sur une seule ligne.

@ + et bonne journée.

**pwrdwnsys** · 31/01/2019, 08h08

Il y a aussi les fonctions "row_number() over(partition by {regroupementi} order by {tri})" et "union all". Je n'ai pas d'exemple simple sous la main, mais tu dois trouver sur le net.

**LudoV63** · 31/01/2019, 08h12

D'acc ça marche, je vais tester tout ça Merci encore.

**Darkzinus** · 31/01/2019, 10h56

Envoyé par escartefigue

Jamais utilisé, quelques exemples avec un échantillon des données en entrée et du résultat obtenu seraient les bienvenus

J'avais ça (peu lisible mais qui marchait, je l'ai "anonymisé" pour des raisons de confidentialité) sur une plateforme AS400/fichiers (pas du DB2) :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
select                                
  CLEREGROUP,
  xmlserialize(xmlagg(xmltext(concat(virg,                          
  DATA)))          
  as varchar( 160 ) ccsid 297 ), 2) DATA
  FROM TABLE
  cross join  qtemp/litvirg virg
group by
  CLEREGROUP

nous avons une table qui se présenterait comme ceci :
CLEREGROUP DATA
01 titi
01 toto
02 tutu
02 tata

qtemp/litvirg contient une ","
du coup ça me donne une structure avec la cle et les données séparée par des virgules :
01 titi, toto
02 tutu, tata

J'espère que c'est plus clair.
Il y sans doute des moyens plus simples pour arriver à ce résultat.

Le LISTAGG proposé plus haut par BERNARD semble beaucoup plus simple (mais inconnu sur ma version de l'AS400)!

**LudoV63** · 31/01/2019, 11h01

Oui en effet le LISTAGG semble plus simple mais ma requête est vraiment longue et du coup je n'arrive pas à implémenter le LISTAGG ...

**LudoV63** · 31/01/2019, 13h12

Voici un extrait de ma requête :

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
SELECT DISTINCT
    SC.ETAB.RNE AS RNE,
	REPLACE(SC.EL_STR.C_STR,' ', '-') AS division,
 
**************************
    SC.MAT_ENS.GEST_MATIERE_ENS AS options,
**************************
 
	LTRIM(SC.E.NOM_EL) AS nom_famille,
	SC.E.NOM_USAGE_EL AS nom_usage,
	SC.E.EL_ID AS ID_EL,
	TO_CHAR(SC.E.DATE_NAISS_ELE, 'DD/MM/YYYY') AS date_naissance,
	SC.E.TEL_PERSL AS portable_EL
 
FROM SC.EL
INNER JOIN SC.OPTION_EL ON (SC.OPTION_EL.EL_ID = SC.E.EL_ID)
INNER JOIN SC.MAT_ENS ON (SC.MAT_ENS.MAT_ENS_ID = SC.OPTION_EL.MAT_ENS_ID)
FULL JOIN SC.EL_PERS_ADR ON (SC.EL_PERS_ADR.EL_ID = SC.E.EL_ID)
FULL JOIN SC.ADR ON (SC.PERS.ADR_ID = SC.ADR.ADR_ID)
 
WHERE SC.ETAB.RNE IN ('xxxxxxxA')
 
AND
SC.E.DATE_SORTIE_ETAB IS NULL
 
**************************
GROUP BY SC.MAT_ENS.MAT_ENS_ID
**************************
 
ORDER BY SC.ETAB.RNE ASC;

Les 2 lignes contenues dans les *************** sont celles que je dois intégrer pour récupérer les options des élèves.

En écrivant juste :

Code :

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

SC.MAT_ENS.GEST_MATIERE_ENS AS options,

Mes 3 options apparaissent sur mon élève test.

Si vous avez une idée avec "xmlserialize" ou "LISTAGG" je suis preneur car je n'y arrive pas là ...

D'avance merci à vous.

**escartefigue** · 31/01/2019, 13h52

Envoyé par bernard59139

Je prefererais utiliser LISTAGG https://www.ibm.com/support/knowledg...f_listagg.html
a+

Applicable uniquement depuis la V12

**Darkzinus** · 31/01/2019, 13h53

Envoyé par escartefigue

Applicable uniquement depuis la V12

J'avais du regarder à l'époque et effectivement je n'avais pas pu l'utiliser pour cette raison. D'où ma méthode "barbare".

**Darkzinus** · 31/01/2019, 15h11

Tu peux essayer ceci (il peut y avoir une erreur vu que je ne peux pas tester) :

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
 
SELECT DISTINCT
    SC.ETAB.RNE AS RNE,
	REPLACE(SC.EL_STR.C_STR,' ', '-') AS division,
*	
**************************
        xmlserialize(xmlagg(xmltext(SC.MAT_ENS.GEST_MATIERE_ENS))) AS option 
**************************
*
	LTRIM(SC.E.NOM_EL) AS nom_famille,
	SC.E.NOM_USAGE_EL AS nom_usage,
	SC.E.EL_ID AS ID_EL,
	TO_CHAR(SC.E.DATE_NAISS_ELE, 'DD/MM/YYYY') AS date_naissance,
	SC.E.TEL_PERSL AS portable_EL
	SC.MAT_ENS.MAT_ENS_ID
*
FROM SC.EL
INNER JOIN SC.OPTION_EL ON (SC.OPTION_EL.EL_ID = SC.E.EL_ID)
INNER JOIN SC.MAT_ENS ON (SC.MAT_ENS.MAT_ENS_ID = SC.OPTION_EL.MAT_ENS_ID)
FULL JOIN SC.EL_PERS_ADR ON (SC.EL_PERS_ADR.EL_ID = SC.E.EL_ID)
FULL JOIN SC.ADR ON (SC.PERS.ADR_ID = SC.ADR.ADR_ID)
*
WHERE SC.ETAB.RNE IN ('xxxxxxxA')
*
AND
SC.E.DATE_SORTIE_ETAB IS NULL
*
 
GROUP BY 
************************************************
SC.ETAB.RNE,
REPLACE(SC.EL_STR.C_STR,' ', '-'),
LTRIM(SC.E.NOM_EL),
SC.E.NOM_USAGE_EL,
SC.E.EL_ID,
TO_CHAR(SC.E.DATE_NAISS_ELE, 'DD/MM/YYYY'),
SC.E.TEL_PERSL
************************************************
*
ORDER BY SC.ETAB.RNE ASC;

**LudoV63** · 31/01/2019, 15h13

Je vais tester de suite et "tenter" de corriger les éventuels beugs mdr

Merci beaucoup, c'est sympa !!!

**LudoV63** · 31/01/2019, 15h25

Je déplace le REPLACE et le TRIM et les autres dans le GROUP BY ??????
Pourquoi donc ?

Ca ne fonctionne pas ....

[Code: -4462, SQL State: ] [jcc][10234][10927][4.22.29] SQL transmis sans jeton. ERRORCODE=-4462, SQLSTATE=null

**LudoV63** · 31/01/2019, 15h42

Je vous communique la requête un peu plus complète si ça peut aider car de mon côté ce n'est pas encore ça :

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
SELECT DISTINCT
        BDD.ETAB_UAJ.CODE_RNE AS RNE,
	REPLACE(BDD.ELEMENT_STRUCTURE.CODE_STRUCTURE,' ', '-') AS division,
	*********************
        xmlserialize(xmlagg(xmltext(BDD.MATIERE_ENSEIGNEE.GEST_MATIERE_ENS))) AS option,
	*********************
	REPLACE(REPLACE(BDD.ELEVE.SEXE_ID, '1', 'M'), '2', 'F') AS civilite,
	LTRIM(BDD.ELEVE.NOM_ELEVE) AS nom_famille,
	BDD.ELEVE.NOM_USAGE_ELEVE AS nom_usage,
	COALESCE(BDD.ELEVE.PRENOM_ELEVE,'')||' '||
	COALESCE(BDD.ELEVE.PRENOM2_ELEVE,'')||' '||
	COALESCE(BDD.ELEVE.PRENOM3_ELEVE,'') as prenoms,
	BDD.ELEVE.ID_NATIONAL AS ine,
	BDD.ELEVE.ELEVE_ID AS ID_ELEVE,
	BDD.PAYS.CODE_PAYS AS pays_naissance,
	TO_CHAR(BDD.ELEVE.DATE_NAISS_ELE, 'DD/MM/YYYY') AS date_naissance,
	BDD.DEPT.CODE_DEPARTEMENT AS depCOM_naissance,	
	CASE WHEN BDD.COMMUNE_INSEE.CODE_COMMUNE_INSEE ISNULL THEN BDD.ELEVE.VILLE_NAISSANCE ELSE BDD.COMMUNE_INSEE.CODE_COMMUNE_INSEE END AS commune_naissance,
	(SELECT BDD.PAYS.CODE_PAYS
	       FROM BDD.PAYS
	       WHERE BDD.PAYS.PAYS_ID = BDD.ELEVE.NATIONALITE_PAYS_ID) AS nationalite,
	CASE WHEN BDD.ELEVE.ADRESSE_ID IS NULL THEN (SELECT BDD.PAYS.CODE_PAYS FROM BDD.PAYS WHERE BDD.PAYS.PAYS_ID = (SELECT BDD.ADRESSE.PAYS_ID FROM BDD.ADRESSE WHERE BDD.ADRESSE.ADRESSE_ID = BDD.PERSONNE.ADRESSE_ID))
	       ELSE (SELECT BDD.PAYS.CODE_PAYS FROM BDD.PAYS WHERE BDD.PAYS.PAYS_ID = (SELECT BDD.ADRESSE.PAYS_ID FROM BDD.ADRESSE WHERE BDD.ADRESSE.ADRESSE_ID = BDD.ELEVE.ADRESSE_ID))
	       END AS pays_residence,	
	BDD.ELEVE.TEL_PERSONNEL AS portable_eleve,
	BDD.ELEVE.EMAIL_ELEVE AS email_pers_a_contacter,
	BDD.ELEVE.TEL_PORTABLE AS portable_pers_a_contacter
 
FROM BDD.ELEVE
INNER JOIN BDD.MEF ON (BDD.MEF.MEF_ID = BDD.ELEVE.MEF_ID)
INNER JOIN BDD.OPTION_ELEVE ON (BDD.OPTION_ELEVE.ELEVE_ID = BDD.ELEVE.ELEVE_ID)
INNER JOIN BDD.MATIERE_ENSEIGNEE ON (BDD.MATIERE_ENSEIGNEE.MATIERE_ENSEIGNEE_ID = BDD.OPTION_ELEVE.MATIERE_ENSEIGNEE_ID)
INNER JOIN BDD.ETAB_UAJ ON (BDD.ELEVE.ETAB_UAJ_ID = BDD.ETAB_UAJ.ETAB_UAJ_ID)
FULL JOIN BDD.COMMUNE_INSEE ON (BDD.ELEVE.COMMUNE_INSEE_ID = BDD.COMMUNE_INSEE.COMMUNE_INSEE_ID)
FULL JOIN BDD.DEPT ON (BDD.DEPT.DEPT_INSEE_ID = BDD.COMMUNE_INSEE.DEPT_INSEE_ID)
INNER JOIN BDD.PAYS ON (BDD.PAYS.PAYS_ID = BDD.ELEVE.PAYS_ID)
INNER JOIN BDD.ELEVE_STRUCTURE ON (BDD.ELEVE_STRUCTURE.ELEVE_ID = BDD.ELEVE.ELEVE_ID)
INNER JOIN BDD.ELEMENT_STRUCTURE ON (BDD.ELEMENT_STRUCTURE.STRUCTURE_ID = BDD.ELEVE_STRUCTURE.STRUCTURE_ID)
FULL JOIN BDD.ELEVE_PERSONNE_ADRESSE ON (BDD.ELEVE_PERSONNE_ADRESSE.ELEVE_ID = BDD.ELEVE.ELEVE_ID)
FULL JOIN BDD.PERSONNE ON (BDD.ELEVE_PERSONNE_ADRESSE.PERSONNE_ID = BDD.PERSONNE.PERSONNE_ID)
FULL JOIN BDD.ADRESSE ON (BDD.PERSONNE.ADRESSE_ID = BDD.ADRESSE.ADRESSE_ID)
 
WHERE BDD.ETAB_UAJ.CODE_RNE IN ('XXXXXXXA')
 
AND
BDD.ELEVE.DATE_SORTIE_ETAB IS NULL
 
*********************
GROUP BY
BDD.ETAB_UAJ.CODE_RNE,
REPLACE(BDD.ELEMENT_STRUCTURE.CODE_STRUCTURE,' ', '-'),
LTRIM(BDD.ELEVE.NOM_ELEVE),
BDD.ELEVE.NOM_USAGE_ELEVE,
BDD.ELEVE.ELEVE_ID,
TO_CHAR(BDD.ELEVE.DATE_NAISS_ELE, 'DD/MM/YYYY'),
BDD.ELEVE.TEL_PERSONNEL
*********************
 
ORDER BY BDD.ETAB_UAJ.CODE_RNE ASC;

J'ai l'erreur -440 qui apparaît :

Code :

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

1
2
1) [Code: -440, SQL State: 42884]  La routine autorisée appelée "XMLSERIALIZE" de type "FUNCTION" et dont les arguments sont compatibles est introuvable.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.22.29
2) [Code: -727, SQL State: 56098]  Une erreur s'est produite lors d'une action système implicite de type "2". Les informations renvoyées pour l'erreur comprennent le SQLCODE "-440", le SQLSTATE "42884" et les jetons de message "XMLSERIALIZE|FUNCTION".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.22.29

**Darkzinus** · 31/01/2019, 15h46

En fait le group by doit contenir toutes tes colonnes autres que celle qui contient des données en ligne (comme quand on fait une somme). Sinon ça n'a pas de sens.
Mais il semblerait que ta plateforme DB2 ne soit pas compatible avec la fonction XMLSERIALIZE (si l'on regarde le message d'erreur).

**LudoV63** · 31/01/2019, 15h47

Je l'ignorais mais j'en prends note, merci !

**LudoV63** · 31/01/2019, 15h49

Envoyé par Darkzinus

En fait le group by doit contenir toutes tes colonnes autres que celle qui contient des données en ligne (comme quand on fait une somme). Sinon ça n'a pas de sens.
Mais il semblerait que ta plateforme DB2 ne soit pas compatible avec la fonction XMLSERIALIZE (si l'on regarde le message d'erreur).

Et il existerait une alternative ?? MAJ de DBVisualiser ? Ou autre syntaxe ?

**Darkzinus** · 31/01/2019, 15h54

Envoyé par LudoV63

Et il existerait une alternative ?? MAJ de DBVisualiser ? Ou autre syntaxe ?

Là ça n'est pas l'outil en cause, c'est la version de DB2. DBVisualiser n'est pas en cause. Je ne connais pas d'autre moyen mais quelqu'un d'autre ici peut avoir une autre idée (je ne suis pas expert en SQL même si je pratique beaucoup).

Récupérer plusieurs lignes et les afficher dans une seule colonne et un seul champ

DB2

Discussions similaires

Partager

Partager