Discussion :

[Zugg]

Bonjour,

J'ai un problème d'ordre lorsque je fais de l'aggrégation de valeurs.
Voici un exemple de données :

Code :

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

1
2
3
4
5
select 'USER1000' Usr,2 Grp, 'Info 2' Info, '30/08/2011 12:13:11' DateHeure, 'U-0013' Nom, 'A' Code from dual union all
select 'USER0002',null,null,null,null,null from dual union all
select 'USER1111',1, 'Info 1', '30/08/2011 12:20:07', 'U-1703', 'A' from dual union all
select 'USER1111',1, 'Info 1', '30/08/2011 12:20:23', 'U-1902', 'B' from dual union all
select 'USER1111',1, 'Info 1', '30/08/2011 12:22:10', 'U-2026', 'A' from dual

USER1000 2 Info 2 30/08/2011 12:13:11 U-0013 A
USER0002
USER1111 1 Info 1 30/08/2011 12:20:07 U-1703 A
USER1111 1 Info 1 30/08/2011 12:20:23 U-1902 B
USER1111 1 Info 1 30/08/2011 12:22:10 U-2026 A

Je souhaite grouper les valeurs suivant USR, GRP et INFO et concaténer les NOM et concaténer les CODE

voici un exemple de requête pour grouper et concaténer les valeurs :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
with q as (
select 'USER1000' Usr,2 Grp, 'Info 2' Info, '30/08/2011 12:13:11' DateHeure, 'U-0013' Nom, 'A' Code from dual union all
select 'USER0002',null,null,null,null,null from dual union all
select 'USER1111',1, 'Info 1', '30/08/2011 12:20:07', 'U-1703', 'A' from dual union all
select 'USER1111',1, 'Info 1', '30/08/2011 12:20:23', 'U-1902', 'B' from dual union all
select 'USER1111',1, 'Info 1', '30/08/2011 12:22:10', 'U-2026', 'A' from dual)
select USR, GRP, INFO, min(DATEHEURE) DATE_MIN,
       wm_concat(NOM) NOMS,
       wm_concat(CODE) CODES
 from q
group by USR, GRP, INFO

Et en résultat j'obtiens :

USER0002
USER1000 2 Info 2 30/08/2011 12:13:11 U-0013 A
USER1111 1 Info 1 30/08/2011 12:20:07 U-1703,U-2026,U-1902 A,B,A

Le problème c'est que pour le USER1111, je veux les noms dans l'ordre et les codes associés dans l'ordre : U-1703,U-1902,U-2026 et en code : A,B,A, car les 2 infos sont liées et il faut qu'elles soient dans le même ordre, dans l'ordre de la DateHeure.

Je suis sous Oracle 10.2.0.4.0, et j'ai essayé sous Oracle 11.2.0.1.0 et l'ordre de concaténation change entre les versions.
J'ai essayé de mettre un order by :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
with q as (
select 'USER1000' Usr,2 Grp, 'Info 2' Info, '30/08/2011 12:13:11' DateHeure, 'U-0013' Nom, 'A' Code from dual union all
select 'USER0002',null,null,null,null,null from dual union all
select 'USER1111',1, 'Info 1', '30/08/2011 12:20:07', 'U-1703', 'A' from dual union all
select 'USER1111',1, 'Info 1', '30/08/2011 12:20:23', 'U-1902', 'B' from dual union all
select 'USER1111',1, 'Info 1', '30/08/2011 12:22:10', 'U-2026', 'A' from dual)
select USR, GRP, INFO, min(DATEHEURE) DATE_MIN,
       wm_concat(NOM) NOMS,
       wm_concat(CODE) CODES
from (select * from q order by USR, GRP, INFO, DATEHEURE)
group by USR, GRP, INFO

Mais ça ne change rien.

Comment faire ?

Merci

[mnitu]

wm_concat/stragg ne trie pas.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH q AS (
SELECT 'USER1000' Usr,2 Grp, 'Info 2' Info, '30/08/2011 12:13:11' DateHeure, 'U-0013' Nom, 'A' Code FROM dual union ALL
SELECT 'USER0002',NULL,NULL,NULL,NULL,NULL FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:20:07', 'U-1703', 'A' FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:20:23', 'U-1902', 'B' FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:22:10', 'U-2026', 'A' FROM dual)
SELECT USR, GRP, INFO, min(DATEHEURE) DATE_MIN,
       cast(collect(NOM order by dateheure)  as varchar2_tt) NOMS,
       cast(collect(CODE  order by dateheure)  as varchar2_tt) CODES
FROM (SELECT * FROM q ORDER BY USR, GRP, INFO, DATEHEURE)
GROUP BY USR, GRP, INFO

[Zugg]

Oui l'aggrégation ne trie pas en effet, d'où mon problème.

La solution proposée, suppose la création du type :

Code :

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

create type varchar2_tt as table of VARCHAR2(100);

Mais au final, j'obtiens pas mes éléments sous forme d'une chaine, comment faire ?

[mnitu]

Vous pouvez trouver une réponse dans "the collect function in 10g"

[Zugg]

merci mnitu, tout est bien expliqué dans ton lien.

Sinon, après avoir tourné la requête dans tous les sens j'ai une autre solution qui ne nécessite aucun type ni fonction :

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
with q as (
select 'USER1000' Usr,2 Grp, 'Info 2' Info, '30/08/2011 12:13:11' DateHeure, 'U-0013' Nom, 'A' Code from dual union all
select 'USER0002',null,null,null,null,null from dual union all
select 'USER1111',1, 'Info 1', '30/08/2011 12:20:07', 'U-1703', 'A' from dual union all
select 'USER1111',1, 'Info 1', '30/08/2011 12:20:23', 'U-1902', 'B' from dual union all
select 'USER1111',1, 'Info 1', '30/08/2011 12:22:10', 'U-2026', 'A' from dual)
select USR, GRP, INFO, min(DATEHEURE) DATE_MIN,
       max(NOMS) NOMS,
       max(CODES) CODES
from (
select  USR, GRP, INFO, DATEHEURE, 
       wm_concat(NOM) over (partition by USR, GRP order by USR, GRP, DATEHEURE) NOMS,
       wm_concat(CODE) over (partition by USR, GRP order by USR, GRP, DATEHEURE) CODES
       from q )
group by USR, GRP, INFO

à priori ça donne bien le résultat voulu.
merci

[mnitu]

Très bien. Sur le même site: listagg function in 11g release 2