Changer le séparateur de WM_CONCAT fonction d'Oracle 11gR2

Normalement, WM_CONCAT est une fonction d'agrégation que les valeurs de retour de la table séparés par des virgules, comme ici.

Supposons que j'ai une table foo comme ceci:

col_id     | col_text


111        | This

111        | is

111        | a

111        | test.

Si j'utilise cette requête:

SELECT CAST(WM_CONCAT(col_text) AS VARCHAR2(100)), col_id FROM foo

le résultat serait

This, is, a, test.

Est-il possible de modifier le séparateur(',') à d'autres personnages comme '.' ou '|' de la WM_CONCAT() fonction?

Ou de créer une fonction définie par l'utilisateur qui peut être exécuté comme WM_CONCAT()?

Si vous êtes sur 11gR2, alors vous devriez utiliser les listagg à la place. Si non il y a divers articles sur la façon d'écrire votre propre version de souvent liés les uns - mais vous avez besoin de spécifier la version que vous utilisez.
11gR2? Comment faire pour déterminer si je suis en utilisant 11gR2? Quand je lance SELECT * FROM V$VERSION; j'ai obtenu ceci: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.
Le 11.2 partie de la chaîne de version signifie que vous avez 11g Release 2, de sorte que vous aurez listagg disponibles. (La version de base a été 11.2.0.1 si vous avez des correctifs appliqués sur le dessus de cela). Vous pourriez aussi tout simplement essayer de l'utiliser de cours *8-)
Mais j'ai un autre problème. Je ne peux pas simplement essayer parce que de la requête, j'ai créé plus tôt. Permettez-moi de mettre à jour ma question.
Ne pouvez-vous pas simplement exécuter @peterm de la version à la place? Dans votre version embarquée, il suffit de remplacer CAST (WM_CONCAT (C.CUST_NAME) AS VARCHAR2 (1500)) avec LISTAGG(C.CUST_NAME, '|') WITHIN GROUP (ORDER BY C.CUST_NAME). Vous pouvez cast ou substr que si vous avez vraiment besoin, mais il n'est probablement pas nécessaire.

OriginalL'auteur Christian Mark | 2013-05-28

  1. 14

    Vous pouvez utiliser LISTAGG.

    SELECT col_id, 
       LISTAGG(col_text, '|') WITHIN GROUP (ORDER BY col_text) text
  FROM table1
 GROUP BY col_id

    De sortie:

    | COL_ID |            TEXT |
----------------------------
|    111 | This|a|is|test. |

    SQLFiddle

    Mise à JOUR Si vous avez besoin pour obtenir distinctes des valeurs de texte dans une liste 

    SELECT col_id, 
       LISTAGG(col_text, '|')
         WITHIN GROUP (ORDER BY col_text) text
  FROM 
(
  SELECT DISTINCT col_id, col_text
    FROM table1
)
 GROUP BY col_id

    SQLFiddle

    Je l'ai maintenant. Merci beaucoup @peterm.
    J'ai un autre problème. Comment puis-je sélectionner des TEXT?
    Vous êtes les bienvenus. Voir mise à jour de réponse.
    Si je peux upvote cette réponse encore une fois, je vais upvote ce. Mais je ne peux pas.:) Je vous remercie encore.
    Heureux, il a aidé 🙂

    OriginalL'auteur peterm

  2. 4

    Problème avec LISTAGG qu'il renvoie varchar2 et est limité à 4000 octets

    SELECT LISTAGG(LEVEL, CHR(10)) WITHIN GROUP (ORDER BY NULL)
  FROM Dual
CONNECT BY LEVEL < 2000

ORA-01489 Result of string concat is too large

    J'ai trouvé une solution de contournement, mais il semble laid et mutch est plus lent

    SELECT EXTRACT(XMLTYPE('<doc>' || XMLAGG(XMLTYPE('<ln>' || LEVEL || CHR(10) || '</ln>')).GetClobVal() || '</doc>'), '/doc/ln/text()').GetClobVal()
  FROM Dual
CONNECT BY LEVEL < 2000

    OriginalL'auteur Žilvinas

  3. 4

    Est-il possible de modifier le séparateur(',') à d'autres caractères comme '.' ou '|' de la WM_CONCAT() la fonction?

    Ne pas utiliser WM_CONCAT puisque c'est un fonctionnalité non documentée et il a été retiré à partir de la dernière 12c version. Toute application qui a été en s'appuyant sur wm_concat fonction ne fonctionnera pas une fois mis à niveau vers 12c. Voir Pourquoi ne pas utiliser WM_CONCAT fonction dans Oracle?

    SQL> select banner from v$version where rownum = 1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> SELECT object_name
  2  FROM dba_objects
  3  WHERE owner='WMSYS'
  4  AND object_name LIKE 'WM\_%' ESCAPE '\';

OBJECT_NAME
----------------------------------------------------------------------------
WM_REPLICATION_INFO
WM_RDIFF
WM_PERIOD
WM_PERIOD
WM_OVERLAPS
WM_MEETS
WM_LESSTHAN
WM_LDIFF
WM_INTERSECTION
WM_INSTALLATION
WM_GREATERTHAN
WM_EVENTS_INFO
WM_ERROR
WM_ERROR
WM_EQUALS
WM_DDL_UTIL
WM_DDL_UTIL
WM_CONTAINS
WM_COMPRESS_BATCH_SIZES
WM_COMPRESSIBLE_TABLES

20 rows selected.

    Vous recevrez un “identifiant invalide” erreur:

    SQL> SELECT banner FROM v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SQL> SELECT deptno, wm_concat(ename) FROM emp;
SELECT deptno, wm_concat(ename) FROM emp
               *
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier

    Par conséquent, il est inutile de compter sur une fonctionnalité non documentée qui n'est plus disponible dans les versions les plus récentes.

    Il existe différents agrégation de chaîne techniques:

    • LISTAGG dans 11gR2 et

    Par exemple,

    SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.
    • ROW_NUMBER() et SYS_CONNECT_BY_PATH fonctions dans 9i et

    Par exemple,

    SELECT deptno,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM   (SELECT deptno,
               ename,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
        FROM   emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.
    • Définis par l'utilisateur fonction d'agrégation STRAGG décrit dans AskTom.
    • RECUEILLIR fonction de 10g et

    Quelques exemples par Tim Hall ici.

    OriginalL'auteur Lalit Kumar B

  4. 1

    La suite a fonctionné pour moi sur un ORACLE 10.2.0.5.0 base de données:

    SELECT col_id, replace(wm_concat(col_text), ',', ' ') AS sentence
  FROM foo
 GROUP BY col_id;
    Salut John, cela peut avoir de problème de performances.
    Salut, pouvez-vous donner plus de détails sur la façon dont est l'impact sur les performances?

    OriginalL'auteur John