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