[9i]Concaténation d'une colonne

Bonjour à tous,

Alors voilà mon problème, j'ai une table de 2 colonnes.

Code:

---

1 2 3 4 5 6 7 8 9

---------------------------- | Colonne 1 | colonne 2 | ---------------------------- | A | 1 | | A | 2 | | B | 1 | | B | 3 | ----------------------------

---

Je veux que à chaque valeur distincte de la première colonne la colonne 2 soit transformé en ligne

Code:

---

1 2 3 4 5 6 7

---------------------------- | Colonne 1 | colonne 2 | ---------------------------- | A | 1;2 | | B | 1;3 | ----------------------------

---

Popur celà j'ai trouvé sur internet un ensemble de fonction:
- concat_all
- sort_string

associé à 2 types
- concat_expr
- concat_all_ot

cette ensemble de fonction s'utilise de la fonction suivante.
1) concat_all = concatène toute les valeurs de la colonne2
2) sort_string = tri et élimine les doublons

dans l'exemple donné il n'y a pas de doublons mais dans le vrai cas si et je ne peux faire autrement (c'est un postulat).

bien que le code soit du chinois pour moi, il marche très bien
et je n'avais aucun souci.

Seulement je me suis trouvé à concaténer une colonne qui au cours de la concaténation retournait une variable de longueur supérieur à 4000.
Comme la fonction concat_all utilise des VARCHAR2, le programme plante avant d'avoir pu supprimer les doublons(sort_string) et insérer les valeurs dans la table.


Donc ma question est comment concaténer une colonne qui renvoie une chaine de longueur > 4000 avant la suppression des doublon?

Il suffit de modifier la fonction concat_all pour éliminer les doublons directement.
Donne le code de CONCAT_ALL

PS : Les déclarations max de VARCHAR2
en SQL : VARCHAR2(4000)
en PL/SQL : VARCHAR2(32767)

je vais aller modifier les valeurs définies pour le vachar2. je vous fais un retour.

ces fonctions s'utilisent comme ça:
concat_all(concat_expr(colonne2,';'))

alors les sources :
attention le chinois

concat_all

Code:

---

1 2 3 4

CREATE OR REPLACE FUNCTION Concat_All ( ctx IN concat_expr) RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE AGGREGATE USING concat_all_ot;

---

utilise le type concat_expr
spec:

Code:

---

1 2 3 4 5

CREATE OR REPLACE TYPE concat_expr AS OBJECT ( str VARCHAR2 (4000), del VARCHAR2 (4000), MAP MEMBER FUNCTION mapping_function RETURN VARCHAR2);

---

body:

Code:

---

1 2 3 4 5 6 7 8 9

CREATE OR REPLACE TYPE BODY concat_expr AS MAP MEMBER FUNCTION mapping_function RETURN VARCHAR2 IS BEGIN RETURN str||del; END mapping_function; END;

---

utilise le type concat_all_ot
spec:

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

CREATE OR REPLACE TYPE concat_all_ot AS OBJECT ( str VARCHAR2 (4000), del VARCHAR2 (4000),   STATIC FUNCTION odciaggregateinitialize ( sctx IN OUT concat_all_ot) RETURN NUMBER,   MEMBER FUNCTION odciaggregateiterate ( SELF IN OUT concat_all_ot, ctx IN concat_expr) RETURN NUMBER,   MEMBER FUNCTION odciaggregateterminate ( SELF IN concat_all_ot, returnvalue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,   MEMBER FUNCTION odciaggregatemerge ( SELF IN OUT concat_all_ot, ctx2 concat_all_ot) RETURN NUMBER);

---

body:

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

CREATE OR REPLACE TYPE BODY concat_all_ot AS STATIC FUNCTION odciaggregateinitialize ( sctx IN OUT concat_all_ot) RETURN NUMBER IS BEGIN sctx := concat_all_ot (NULL, NULL); RETURN odciconst.success; END;   MEMBER FUNCTION odciaggregateiterate ( SELF IN OUT concat_all_ot, ctx IN concat_expr) RETURN NUMBER IS BEGIN IF SELF.str IS NOT NULL THEN SELF.str := SELF.str || ctx.del; END IF; SELF.str := TO_CHAR(SELF.str) || TO_CHAR(ctx.str); RETURN odciconst.success; END;   MEMBER FUNCTION odciaggregateterminate ( SELF IN concat_all_ot, returnvalue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS BEGIN returnvalue := SELF.str; RETURN odciconst.success; END;   MEMBER FUNCTION odciaggregatemerge ( SELF IN OUT concat_all_ot, ctx2 IN concat_all_ot) RETURN NUMBER IS BEGIN IF SELF.str IS NOT NULL THEN SELF.str := SELF.str || SELF.del; END IF; SELF.str := SELF.str || ctx2.str; RETURN odciconst.success; END; END;

---

voili voilou

merci beaucoup

ALors:

J'ai modifié la taille des VARCHAR2 dans les spec de
concat_expr et concat_all_ot

mais j'ai un problème:

Code:

---

1 2 3 4 5 6 7 8 9 10 11

MEMBER FUNCTION odciaggregateterminate ( SELF IN concat_all_ot, returnvalue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS BEGIN returnvalue := SELF.str; RETURN odciconst.success; END;

---

returnvalue est déclaré en varchar2 et n'accepte que 4000 caractères.
je ne sais comment changer la valeur.
A la place de varchar2 j'ai mis string mais ça n'a rien changé.

Je t'avouerai que je ne comprend pas tout...
Bon, on peut faire ça à l'ancienne :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

CREATE FUNCTION f_concat (p_col1 IN VARCHAR2, p_sep IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS CURSOR c IS SELECT DISTINCT col2 FROM MATABLE WHERE col1 = p_col1; v_retour VARCHAR2(4000); BEGIN FOR r IN c LOOP v_retour := v_retour || p_sep || r.col2; END LOOP; RETURN SUBSTR(v_retour, NVL(LENGTH(p_sep),0)+1); -- Pour supprimer le premier separateur. END;

---

et tu l'appelles comme ceci

Code:

---

1 2 3

SELECT col1, f_concat(col1,';') as colonne2 FROM MATABLE GROUP BY col1

---

Merci pour la fonction je l'utiliserai en dernier recours
parce que là tel qu'est mon code je ne peux pas.

La concaténation ne s'appuie pas sur une table mais sur une énorme requête
et sert à faire un insert dans une autre table.

donc à mois de faire l'insert dans cette table
puis la concaténation puis la suppression des lignes ayant servi à la concaténation, je ne peux me servir de la requête.
Et j'aimerai éviter autant que possible ces étapes.

A quoi sert le paramètre deterministic??

C'est une fonction qui pour un même paramètre renverra toujours la même chose.
Contre exemple : DBMS_RANDOM.VALUE est une fonction non deterministic.

Ca sert pour les index de fonctions.

Dans le cas du concat : c'est faux en fait, vu que si la table change, la valeur change.


Sinon pour les fonctions de concat_all
peut être en faisant comme ceci :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

MEMBER FUNCTION odciaggregateiterate ( SELF IN OUT concat_all_ot, ctx IN concat_expr) RETURN NUMBER IS BEGIN IF SELF.str IS NOT NULL THEN -- Si l'élément existe déjà : On ne l'insère pas. IF INSTR(ctx.del || SELF.str || ctx.del, ctx.del || ctx.str || ctx.del) > 0 THEN RETURN odciconst.success; END IF; SELF.str := SELF.str || ctx.del; END IF; SELF.str := TO_CHAR(SELF.str) || TO_CHAR(ctx.str);   RETURN odciconst.success;   END;

---

A tester, et faire de même sur le merge. Ce n'est que de la spéculation, je ne connais pas ce genre de fonction (et j'ai pas le temps pour la tester)

Citation:

---

gigigao
A quoi sert le paramètre deterministic??

---

McM je te pose la même question :D
LBO72.

YEEEEEEEEEEHAAAAAAAAAAAAAAAAA ça marche!!!!!
:yaisse2:

Merci Metro Goldwyn Mayer :king:

Bon ça va demander des tests plus poussé mais dans l'absolu ça marche.

@LBO72: il a répondu à ça dans le post du dessus ;)

SI me colègues me laissent tranquille 5 minutes, je vais me plonger longuement dans le code et vous faire un retour sur les tenants et aboutissants de cette fonction.

EN tout cas un gros merci.

on aurait pas pu faire

Code:

---

select col1,concat_all(distinct col2) from t group by col1

---

???

Citation:

---

Envoyé par laurentschneider

on aurait pas pu faire

Code:

---

select col1,concat_all(distinct col2) from t group by col1

---

???

---

Bon alors ok je suis un boulet mais je dois dire que j'avais déjà tester....mal

je faisais:

Code:

---

1 2	select col1,concat_all(concat_expr(distinct col2,';')) from t group by col1

---

ce qui ne marchais pas mais je n'avais pas tester le

Code:

---

1 2	select col1,concat_all(distinct concat_expr(col2,';')) from t group by col1

---

qui marche beaucoup mieux

:aie:

En même temps je te remercie de ton intervention mais tu m'as cassé tout mon délire ;)

Juste une chose la solution de McM semble plus performante en temps.

Ah complexité quand tu nous tiens......