Oracle10 : concatener champs texte de plusieurs lignes

**isn44** · 19/11/2007, 16h21

bonjour,

Soit les données suivantes:

Code :

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

1
2
3
4
blocNote     ma_date          affaire
aa              01/02/2007      144000
bb              01/03/2007      144000
cc              19/11/2007      144000

Résultat attendu:

Code :

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

1
2
blocNote     ma_date          affaire
aa bb cc     19/11/2007      144000

Je souhaite obtenir 1 seule ligne en prenant le max de la date et en concaténant tous mes BlocNotes (avec un espace entre chaque....).

Pour la date, je pense pouvoir utiliser :

Code :

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

max(MA_DATE) over(partition by affaire)

Mais comment faire pour concatener les valeurs de blocnote de chaque ligne ?

Si quelqu'un a une idée, ce serait cool !

Merci d'avance !

**LBO72** · 19/11/2007, 16h43

A mon avis, il faut que passes par du PlSql et utilser un curseur

LBO72

**isn44** · 19/11/2007, 16h50

ok...

domage que cela ne puisse pas se faire dans un select avec une fonction analytique...

Merci quand même.

Je reste ouverte à d'autres idées.

**salim11** · 19/11/2007, 18h30

Salut,

Essaie ce code, mais il faut changer le nom de la table

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT     SUBSTR (SYS_CONNECT_BY_PATH (blocnote, ' '), 2) name_list, ma_date
      FROM 
    (SELECT blocnote, affaire, COUNT (*) OVER (PARTITION BY affaire) cnt,
                   MAX (ma_date) OVER (PARTITION BY affaire) ma_date,
                   ROW_NUMBER()OVER(PARTITION BY AFFAIRE ORDER BY blocnote) seq
              FROM note_tab
             WHERE blocnote IS NOT NULL)                             
WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq AND PRIOR affaire = affaire;

**mnitu** · 19/11/2007, 22h20

Non ça ne marche pas

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
 
create table t_p
(
  blocNote     varchar2(2),
  ma_date      date,
  affaire      Number(6)
)
/
 
insert into t_p values('aa','01/02/2007',144000);
insert into t_p values('bb','01/03/2007',144000);
insert into t_p values('cc','19/11/2007',144000);
insert into t_p values('cc','19/11/2007',144001);
insert into t_p values('dd','19/11/2007',144001);
insert into t_p values('ee','10/12/2007',144001);
/
SELECT SUBSTR (SYS_CONNECT_BY_PATH (blocnote, ' '), 2) name_list, ma_date
      FROM (SELECT blocnote, affaire, COUNT (*) OVER (PARTITION BY affaire) cnt,
                   MAX (ma_date) OVER (PARTITION BY affaire) ma_date,
                   ROWNUM seq
              FROM t_p
             WHERE blocnote IS NOT NULL)
     WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq AND PRIOR affaire = affaire
/
 
NAME_LIST
-----------------------------------------
 
MA_DATE
--------
aa bb cc
19/11/07

Par contre cette solution marche si tu connais d'avance le max(blocnote) group by affaire. Sinon, des autres solutions sont envisageable (y compris celles similaire à la proposition de salim11, valable à partir d'Oracle 10 seulement)

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 affaire, max(ma_date),
       max(decode(seq,1,blocnote,null)),
       max(decode(seq,2,blocnote,null)),
       max(decode(seq,3,blocnote,null))
from (
  select affaire, ma_date, blocnote, row_number() over (partition by affaire order by ma_date) seq
  from t_p)
where seq <= 3
group by affaire
/
   AFFAIRE MAX(MA_D MA MA MA
---------- -------- -- -- --
    144000 19/11/07 aa bb cc
    144001 10/12/07 cc dd ee

**salim11** · 20/11/2007, 01h39

Salut,

Merci pour ta remarque, mais j'ai corrigé ma requête même avant que tu post ton code

.

Voici la démonstration que ça marche bien

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
 
SQL>  SELECT   affaire,  SUBSTR (SYS_CONNECT_BY_PATH (blocnote, ' '), 2) name_list, ma_date
  2         FROM
  3       (SELECT blocnote, affaire, COUNT (*) OVER (PARTITION BY affaire) cnt,
  4                      MAX (ma_date) OVER (PARTITION BY affaire) ma_date,
  5                      ROW_NUMBER()OVER(PARTITION BY AFFAIRE ORDER BY blocnote) seq
  6                 FROM t_p
  7                WHERE blocnote IS NOT NULL)
  8   WHERE seq = cnt
  9   START WITH seq = 1
 10   CONNECT BY PRIOR seq + 1 = seq AND PRIOR affaire = affaire;
 
        AFFAIRE NAME_LIST                               MA_DATE
-------------- ---------------------------------------- ------------
        144000 aa bb cc                                 19/11/2007
        144001 cc dd ee                                 10/12/2007
 
SQL>

**orafrance** · 20/11/2007, 08h50

avec des perfs probablement plus prometteuse en plus

**mnitu** · 20/11/2007, 09h18

Envoyé par salim11

Salut,

Merci pour ta remarque, mais j'ai corrigé ma requête même avant que tu post ton code

.
...

Bonjour M Salim 11,

Si vous relisez ce que j’ai écrit vous pouvez noter que je n’ai ne pas douté de la faisabilité de votre solution

J’ai simplement remarqué que le code posté ne donne pas le bon résultat et que votre solution est valable à partir d’Oracle 10 seulement.
Par contre pourriez-vous m’expliquer que est-ce que vous voulez dire par « j'ai corrigé ma requête même avant que tu post ton code » ?

**isn44** · 20/11/2007, 09h26

Merci à tous !!

Je vois que les challenges techniques ont toujours des preneurs !
Heureusement qu'il y a des pointures comme vous !
Merci mille fois, car ce n'était absolument pas évident pour une débutante+ comme moi .

**laurentschneider** · 20/11/2007, 12h01

Code :

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

1
2
3
4
create table t as (
select 'aa' blocnote,to_date('01/02/2007','DD/MM/YYYY') ma_date,44000 affaire from dual union all
select 'bb',to_date('01/03/2007','DD/MM/YYYY'),44000 from dual union all
select 'cc',to_date('19/11/2007','DD/MM/YYYY'),44000 from dual )

Code :

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

1
2
3
4
5
6
select
  wmsys.wm_concat(blocnote) blocnote,
  max(ma_date) ma_date,
  affaire
from t
group by affaire;

Code :

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

1
2
3
BLOCNOTE MA_DATE                AFFAIRE
-------- ------------------- ----------
aa,bb,cc 2007-11-19_00:00:00      44000

**orafrance** · 20/11/2007, 12h09

la requête qui calme tout le monde

**isn44** · 20/11/2007, 12h09

Bonjour,
c'est quoi :

Code :

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

wmsys.wm_concat()

**SheikYerbouti** · 20/11/2007, 12h10

c'est dispo depuis quelle version?

**LBO72** · 20/11/2007, 13h07

Trés trés fort Laurent .....
Toute mon admiration

LBO72.

**laurentschneider** · 20/11/2007, 15h00

Envoyé par isn44

Bonjour,
c'est quoi :

Code :

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

wmsys.wm_concat()

c'est une fonction d'aggrégat. Je l'ai découvert par hasard en cherchant :

Code :

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

select owner,object_name from dba_procedures where aggregate='YES';

Je ne peux pas dire dans quelle version c'est apparu, ce n'est pas documenté et les user-defined-aggregates (UDAG) sont apparues en 9iR1. Si quelqu'un a une 9iR1 pour confirmer, merci

**mnitu** · 20/11/2007, 15h31

Merci Laurent, cella je ne le connaissais pas.
Par contre il faut avoir installé Oracle Workspace Manager d'abord

**laurentschneider** · 20/11/2007, 15h37

Envoyé par mnitu

Merci Laurent, cella je ne le connaissais pas.
Par contre il faut avoir installé Oracle Workspace Manager d'abord

c'est très juste! cependant, si tu utilises DBCA alors WM est toujours installé.

**salim11** · 20/11/2007, 16h18

Salut,

Merci Laurent, Tu nous proposes toujours de solutions extraordinaires

.

J’apprends toujours de toi de nouvelles techniques.

Salim.

**mnitu** · 20/11/2007, 18h17

Envoyé par laurentschneider

c'est très juste! cependant, si tu utilises DBCA alors WM est toujours installé.

Mon DBA n'utilise pas DBCA

Mais je voudrais vous demander si vous envisages d'utiliser votre solution dans un environnement de production ?

**laurentschneider** · 20/11/2007, 19h15

Envoyé par mnitu

Mon DBA n'utilise pas DBCA

Mais je voudrais vous demander si vous envisages d'utiliser votre solution dans un environnement de production ?

bon tu peux installer wm manuellement avec @?/rdbms/admin/owminst.plb

j'imagine que la fonction est utilisable en prod, pour être sûr tu peux ouvrir un ticket sur metalink... mais ça a l'air de fonctionner. Tu peux aussi développer ta propre fonction comme asktom/stragg

Oracle10 : concatener champs texte de plusieurs lignes

SQL Oracle

Discussions similaires

Partager

Partager